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