Outer Joins Involving Selection Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If Tabe A is linked to Table B using a left outer join, everything in Table A
displays on a query, even when records don't exist in Table B. So far so
good.
But if I specify a selection criteria for one of the Table B fields, and the
criteria
test fails, the query does not produce a row at all, even for the Table A
data.
Is this how it should work?

If so, how can I set a query up so that I always see Table A data, and Table B
data only when certain criteria are met?

Thanks,
Tom
 
Try adding a criteria for a table A field of Like "*" in the same row
as the table B criteria.
 
The filtering of the Where statement is done after the LEFT JOIN, so it's
too late in your case. You must perform the filtering before the JOIN by
using a subquery, something in the line of:

Select * from tableA left join (select * from tableB where tableB.ppp =
false) as tB on tableA.Id = tB.Id;

Don't forget the semi-comma ; at the end of the Select query as I saw a
report where the omission of ; at the end of queries with OUTER JOIN can
lead to syntaxe errors from Access. Also, on some installations, the
subqueries are delimited by [ ]. and not by parenthesis as in the above
example.
 
If so, how can I set a query up so that I always see Table A data, and Table B
data only when certain criteria are met?
Click to expand...

Add a clause

OR TableB.fieldname IS NULL

to your criteria.

Won't always work ( there might be records where the record exists and the
field IS null ); in that case use the subquery suggestion.

John W. Vinson [MVP]
 
Back
Top