SELECT subset from two tables and LEFT JOIN results

Question

Hi all,

I'm trying to write a bit of SQL for SQLITE that will take a subset from two tables (TableA and TableB) and then perform a LEFT JOIN.

This is what I've tried, but this produces the wrong result:

Select * from TableA 
Left Join TableB using(key)
where TableA.key2 = "xxxx"
AND TableB.key3 = "yyyy"

This ignore cases where key2="xxxx" but key3 != "yyyy".

I want all the rows from TableA that match my criteria whether or not their corresponding value in TableB matches, but only those rows from TableB that match both conditions.

I did manage to solve this by using a VIEW, but I'm sure there must be a better way of doing this. It's just beginning to drive me insane tryng to solve it now.

(Thanks for any help, hope I've explained this well enough).

Answer 1

Select * 
from TableA  
Left Join TableB on TableA.key = TableB.key 
    and TableB.key3 = "yyyy" 
where TableA.key2 = "xxxx" 

Answer 2

YOu have made the classic left join mistake. In most databases if you want a condition on the table on the right side of the left join you must put this condition in the join itself and NOT the where clause. IN SQL Server this would turn the left join into an inner join. I've not used SQl lite so I don't know if it does the same but all records must meet the where clause generally.