TableA Left Join (a subset of) Table B

Question

I want to left join TableA to TableB where a certain condition in tableA is true So I do this type of SQL query

Select * from
TableA Left Join TableB on TableA.fld1 = TableB.fld2
where TableA.fld3 = True

This works OK. Now however I want to do the Join only with certain records in TableB, ie those records in TableB where a certain condition is met, specifically fld4 has false as its value. The reason I want to do this is I want to know which rows in tableA don't have a match in tableB among the rows in tableB where fld4 is false.

If I was to delete all the rows in TableB where fld4 is true and run the above query I'd get the correct result. All I'd need to do is find the rows in the resultant recordset with null in some cell. But if instead of deleting rows from TableB first I alter the query to the one below I get no rows at all returned

Select * from
TableA Left Join TableB on TableA.fld1 = TableB.fld2
where TableA.fld3 = True
and TableB.fld4 = false

If my ramblings make sense can someone tell me what I'm doing wrong? Thanks

Answer 1

You should put the condition in the join clause. When you have a where clause that filters rows on the "right" side of a left join query, you ultimately exclude rows. Try this:

Select * 
from   TableA 
       Left Join TableB 
         on TableA.fld1 = TableB.fld2 
         and TableB.fld4 = false
where  TableA.fld3 = True

Answer 2

Put it in the join clause:

select * from TableA 
left join TableB 
  on TableA.fld1 = TableB.fld2 
 and TableB.fld4 = False
where TableA.fld3 = True

Edit: ah, I missed this:

I want to know which rows in tableA don't have a match in tableB among the rows in tableB where fld4 is false.

Joel's query would work, but since you are not interested in any rows from TableB, a correlated subquery may be cleaner:

select * from TableA
where TableA.fld3 = True 
  and not exists (
    select * from TableB
    where TableA.fld1 = TableB.fld2
      and TableB.fld4 = False
    )

Answer 3

I want to know which rows in tableA don't have a match in tableB among the rows in tableB where fld4 is false.

Then you want to do this:

SELECT * 
FROM TableA
LEFT JOIN TableB on TableA.fld1 = TableB.fld2 AND TableB.fld4 = False
WHERE TableA.fld3 = True
    AND TableB.fld4 IS NULL