ACCESS FEED: Re: [MS_AccessPros] What causes an outer join fail to work when use with remote tables?

Kamis, 11 September 2014

Re: [MS_AccessPros] What causes an outer join fail to work when use with remote tables?

Phucon-

The reason it "fails" is the query engine performs the Join first, then applies the criteria to the result. In your query, you'll get all the rows from AttrTbl and any matching rows from FinTbl as a result of the Join. Where there is no matching row in FinTbl, you get a Null value in all the columns from that table. Now you apply the filter and eliminate all the rows from FinTbl that don't have an Assetsdate of 20140331 - that eliminates all the rows with a Null in them!

You need to filter the FinTbl rows first, then do the Join, like this:

SELECT DISTINCT a.Aid, a.Acity, a.Astate, a.Adate, f.Fid, f.ASSETS, f.AssetsDate
FROM AttrTbl AS a LEFT JOIN

(SELECT Fid, ASSETS, AssetsDate

FROM FinTbl

WHERE Assetsdate = 20140331) AS f

ON a.Aid = f.Fid
WHERE (((a.Aid)=12345) AND ((a.Adate)=99991231);

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Sep 11, 2014, at 4:42 PM, saigonf7q5@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thank you all.

"causes an outer join to fail" I was trying to say my Left Outer Join query display an Inner Join result. Sorry for the confusion.

The table's stored in SQL Server(s), and is located in another state. The tables are big and are read only tables. I would say a hundred thousand rows. The ID field is the indexed field.

My querie's exactly like Crystal discribed. I do have CRITERIA in the outer joined table. Why does it shows only Inner Join result when there's criteria in the query?

Below is the SQL that I've tried.

'the result of this query displays only Inner Join
SELECT DISTINCT a.Aid, a.Acity, a.Astate, a.Adate, f.Fid, f.ASSETS, f.AssetsDate
FROM AttrTbl a LEFT JOIN FinTbl f ON a.Aid = f.Fid
WHERE (((a.Aid)=12345) AND ((a.Adate)=99991231) AND ((f.Assetsdate)=20140331));

'i then tried another approach, do it in 3 steps, then i got the correct result - the Outer Join.

'step 1 get the attribute info
SELECT DISTINCT a.Aid, a.Acity, a.Astate, a.Adate
FROM AttrTbl
WHERE (a.Aid = 12345) And (a.Adate = 99991231)

'step 2 get the financial info, filter by financial date
SELECT f.Fid, f.ASSETS, f.AssetsDate
FROM FnTbl f
WHERE f.dt=20140331;

'step 3 get final output, join the 2 queries
SELECT a.*, f.* FROM qryA a LEFT JOIN qryF ON a.Aid = f.Fid

What are the other better ways to code, besides the 3 steps approach?

Thank you again.

__._,_.___

Posted by: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•