On 4/27/2017 7:15 PM, Joseph L. Casale wrote:
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.
SELECT TableA.ColA Name, B.Value BV, C.Value CV
FROM TableA
LEFT JOIN TableB B ON (TableA.Id=B.Id AND B.Key='BAR')
LEFT JOIN TableB C ON (TableA.Id=C.Id AND C.Key='BAZ')
WHERE TableA.ColB=42
GROUP BY Name
The condition in the ON clause is evaluated while the join is performed;
conceptually, for each pair of rows in the two tables.
The condition in the WHERE clause is evaluated *after* all joins are
performed, further filtering the resultset thus produced. In case of an
outer join, this means it has to be prepared to deal with rows that have
NULL in some columns. If one is not careful, it's easy to accidentally
filter those rows out, thus defeating the point of an outer join.
In other words, when the condition is false in the ON clause of a LEFT
JOIN, the result is the row from left table combined with NULLs for
right table. When the condition is false in WHERE clause, the whole row
is dropped.
For a "normal" inner join, a condition has the same effect either way,
whether it appears in ON or WHERE clause.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
