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' or B.Key is null) AND (C.Key='BAZ' or C.Key is null) GROUP BY Name
-- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Joseph L. Casale > Sent: Thursday, 27 April, 2017 17:15 > To: 'sqlite-users@mailinglists.sqlite.org' > Subject: [sqlite] SQL join syntax > > 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
