Hey guys, Need a pointer some join syntax. I have a couple where TableA is one to many on TableB. TableB structure is three columns Id,Key,Value. For each row I select in Table, I join TableB several times for each Key whose value I want in the single row result. How do you construct the join and predicate so that for when a given key in TableB is not present, the result is null? A left join alone is not enough, the WHERE/AND excludes all data when one of the joins is not satisfied.
SELECT TableA.ColA Name, B.Value BV, C.Value CV FROM TableB LEFT JOIN TableB B ON TableA.Id=B.Id LEFT JOIN TableB C ON TableA.Id=C.Id WHERE TableA.ColB=42 AND B.Key='BAR' AND C.Key='BAZ' GROUP BY Name Works fine when BAR and BAZ values exist in TableB.Key. Thanks guys, jlc _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
