Multiple left joins on multiple tables in one query

Question

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

User · Accepted Answer

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 separating FROM-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:

Two SQL LEFT JOINS produce incorrect result

User · Answer

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

User · Answer

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

[sql] Multiple left joins on multiple tables in one query

Examples related to sql

Examples related to postgresql

Examples related to join

Examples related to left-join

Examples related to multiple-tables