I've got one master
table, which has items stored in multiple levels, parents and childs, and there is a second table which may or may not have additional data. I need to query two levels from my master table and have a left join on my second table, but because of the ordering within my query this will not work.
SELECT something FROM master as parent, master as child
LEFT JOIN second as parentdata ON parent.secondary_id = parentdata.id
LEFT JOIN second as childdata ON child.secondary_id = childdata.id
WHERE parent.id = child.parent_id AND parent.parent_id = 'rootID'
The left join only works with the last table in the from clause, so I am only able to make it work for one of the left joins. In the example above none of the left joins will work because the first left join points towards the first table in the from clause, the second one will never work like this.
How can I make this work?
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 "old style" CROSS JOIN
with comma (,
). I quote 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 preferable, as your case illustrates once again.
And be aware that multiple (LEFT
) JOIN
can multiply rows: