Join Properties

[Dwight]

I am designing a query that joins two tables by SSN. I need to identify
missing records from one of the tables. Table1 has 279 records in it.
Table2 has 3221 records in it. Table2 has two additional fields that I use
in the query to set the criteria. I want to see ALL the records in Table1
and only those records from Table2 where the joined fields are equal. When I
set properties to that selection I expect to see the 279 records from Table1,
however, the query only returns 276. The 276 records are the same records
that returned if I leave the join properties set to "Only include rows where
the joined firelds from tables are equal".

SELECT Table1.SSN, Table2.board_id, Table2.date_withdrawl
FROM Table1 LEFT JOIN Table2 ON Table1.SSN = Table2.board_ssn
WHERE (((Table2.board_id)="gnov2008"));

Does anyone have any idea why this might be happening?

Thanks in advance!

Dwight

 

[Michel Walsh]

Yep. The WHERE clause is applied AFTER the join. Since table2 fields may
appear with NULL, after the join, you have to code accordingly. I suggest:


SELECT Table1.SSN, x.board_id, x.date_withdrawl
FROM Table1 LEFT JOIN (SELECT *
FROM Table2
WHERE board_id="gnov2008") AS x
ON Table1.SSN = x.board_ssn



On this case the sub-query is evaluated first, so the where clause is
performed before the main join is done.






Vanderghast, Access MVP

 

[Dwight]

Thank! That did work, but why did table 2 display nulls when there were not a
nulls in the actual table?

 

[Michel Walsh]

That is because of the nature of the join. The null belong to the RESULT of
the JOIN.


See, you asked all rows from table1 AND to supply fields from table2, for
each of these rows. BUT if there is no matching rows from table2 (the
matching being defined by the ON clause) .. what will you want under those
fields that are supposed to come from table2? "no match", "not found",
"not applicable",... why not just NULL ?




So, in short, the NULLs belong to the result of the JOIN, NOT to the table
!



Hoping it may help,
Vanderghast, Access MVP