Left join table with filter

[Aurillius]

Hi,

I'm trying to create a left join between 2 tables and the right table has a filter. The problem that I'm having is that I'd like to still show all records on the left table and show a blank cell on the right table.
Here is a simplified version of what I have...

SELECT tableA.item
FROM tableA LEFT JOIN tableB ON tableA.item = tableB.item
WHERE (((tableB.date)=#8/31/2006#));

tableA and tableB have the same data in the item field but tableB has a date field with one date that is different.
Thanks,
Mark

 

[r937]

SELECT tableA.item
  FROM tableA 
LEFT 
  JOIN tableB 
    ON tableB.item = tableA.item
   [b]AND[/b] tableB.date = #8/31/2006#

r937.com | rudy.ca

 

[PHV]

SELECT tableA.item
FROM tableA LEFT JOIN (
SELECT * FROM tableB WHERE tableB.date=#8/31/2006#
) AS B ON tableA.item = B.item

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Aurillius]

Thank you very much! The embedded select statement was the key.
Mark