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 · 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

User · 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  quot old style quot  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

[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