Multiple left joins on multiple tables in one query
This kind of query should work - after rewriting with explicit JOIN
syntax:
SELECT something
FROM master parent
JOIN master child ON child.parent_id = parent.id
LEFT JOIN second parentdata ON parentdata.id = parent.secondary_id
LEFT JOIN second childdata ON childdata.id = child.secondary_id
WHERE parent.parent_id = 'rootID';
The tripping wire here is that an explicit JOIN
binds before a comma (,
), which is otherwise equivalent to CROSS JOIN
. The manual here:
In any case
JOIN
binds more tightly than the commas separatingFROM
-list items.
After rewriting the first, all joins are applied left-to-right (logically - Postgres is free to rearrange tables in the query plan otherwise) and it works.
Just to make my point, this would work, too:
SELECT something
FROM master parent
LEFT JOIN second parentdata ON parentdata.id = parent.secondary_id
, master child
LEFT JOIN second childdata ON childdata.id = child.secondary_id
WHERE child.parent_id = parent.id
AND parent.parent_id = 'rootID';
But explicit JOIN
syntax is generally clearer.
And be aware that multiple (LEFT
) JOIN
can multiply rows:
- Two SQL LEFT JOINS produce incorrect result
The JOIN
statements are also part of the FROM
clause, more formally a join_type is used to combine two from_item's into one from_item, multiple one of which can then form a comma-separated list after the FROM
. See http://www.postgresql.org/docs/9.1/static/sql-select.html .
So the direct solution to your problem is:
SELECT something
FROM
master as parent LEFT JOIN second as parentdata
ON parent.secondary_id = parentdata.id,
master as child LEFT JOIN second as childdata
ON child.secondary_id = childdata.id
WHERE parent.id = child.parent_id AND parent.parent_id = 'rootID'
A better option would be to only use JOIN
's, as it has already been suggested.
You can do like this
SELECT something
FROM
(a LEFT JOIN b ON a.a_id = b.b_id) LEFT JOIN c on a.a_aid = c.c_id
WHERE a.parent_id = 'rootID'