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?

Tom

Mixing "old style" (two table names in the FROM clause) with "new style" (LEFT JOIN...ON) is almost certain to end in tears. Rewrite that, and look hard at your WHERE clause, which might be eliminating rows you really don't want eliminated. – Savanna

Does this answer your question? Mixing implicit and explicit JOINs – Stephenstephenie

Answer 1

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 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 clearer.

And be aware that multiple (LEFT) JOIN can multiply rows:

• Two SQL LEFT JOINS produce incorrect result

Stonecrop

Answer 2

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'

Trangtranquada

@DeniseMeander: I don't know why this answer is accepted. It does not add anything new to the old question and - except for the parentheses, which are just noise in this case. And no explanation either. Explicit joins are executed left to right by default (unless join conditions force a different order). – Stonecrop

@ErwinBrandstetter this answer is more clear, cause yours have some spaces in the name of tables, and it is confusing. – Duer

@kahonmlg: There are no spaces in any table names. Maybe you misinterpreted the table aliases. See: https://mcmap.net/q/427658/-date-column-arithmetic-in-postgresql-query – Stonecrop

Having always been mystified by LEFT JOINs, this answer has helped me to understand the accepted answer more clearly from a javascript programming perspective (it was the parentheses that sparked new understanding). Thanks! – Witwatersrand

I prefer the parentheses. Thanks! – Steeve

Answer 3

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.

Tower