Multiple left joins on multiple tables in one query

This kind of query should work - after rewriting with explicit JOIN syntax:

SELECT somethingFROM   master      parentJOIN   master      child ON child.parent_id = parent.idLEFT   JOIN second parentdata ON parentdata.id = parent.secondary_idLEFT   JOIN second childdata ON childdata.id = child.secondary_idWHERE  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 somethingFROM   master parentLEFT   JOIN second parentdata ON parentdata.id = parent.secondary_id,      master childLEFT   JOIN second childdata ON childdata.id = child.secondary_idWHERE  child.parent_id = parent.idAND    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

You can do like this

SELECT somethingFROM    (a LEFT JOIN b ON a.a_id = b.b_id) LEFT JOIN c on a.a_aid = c.c_idWHERE a.parent_id = 'rootID'

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 somethingFROM    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.idWHERE 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.