Outer Joins Involving Selection Criteria

[Tom Glasser]

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

 

[KARL DEWEY]

Try adding a criteria for a table A field of Like "*" in the same row
as the table B criteria.

 

[Sylvain Lafontaine]

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.

 

[John W. Vinson]

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?

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]