Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multiple left joins on multiple tables in one query

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?

like image 506
DeniseMeander Avatar asked Jan 10 '13 15:01

DeniseMeander


People also ask

Can you have two left JOINs in one query?

Yes, indeed! You can use multiple LEFT JOINs in one query if needed for your analysis.

How join multiple tables with LEFT join?

Syntax For Left Join:SELECT column names FROM table1 LEFT JOIN table2 ON table1. matching_column = table2. matching_column; Note: For example, if you have a left table with 10 rows, you are guaranteed to have at least 10 rows after applying join operation on two tables.


2 Answers

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
like image 53
Erwin Brandstetter Avatar answered Oct 02 '22 10:10

Erwin Brandstetter


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' 
like image 27
pradip garala Avatar answered Oct 02 '22 10:10

pradip garala