All Forums Analytics
yunfeizhao 6 posts Joined 02/15
12 Feb 2015
How to make multiple joins?

How to make multiple joins?
I have four tables (tb1-tb4), and each table has the same one column (id). I want an inner join between tb1 and tb2, then left join other tables.
select tb1.id
tb2.id
tb3.id
tb4.id
from tb1, tb2
left join tb3
on tb2.id=tb3.id
left join tb4
on tb2.id=tb4
where tb1.id=tb2.id
;
I want to know the order to put these joins in order to correctly use the on condtion. I recall that I read it somewhere and it says that on condition can be only applied to the tables immediately before and after the key workd left join.
Can you give me some guideline/rule on this?
Thanks
Yunfei

dnoeth 4628 posts Joined 11/04
12 Feb 2015

Hi Yunfei,
if you don't use parenthesis the order of joins is determined by the order of ONs. Within ON you can access all the tables previously joined (before the JOIN) plus the new one (after the JOIN).
In your case you better change the old-style inner join (comma-delimited) to JOIN-syntax (in fact I don't know the rules if you mix old/new):

from tb1 JOIN tb2
  on tb1.id=tb2.id
left join tb3
  on tb2.id=tb3.id
left join tb4
  on tb2.id=tb4

 

Dieter

yunfeizhao 6 posts Joined 02/15
12 Feb 2015

Dieter, Thanks again.
 

You must sign in to leave a comment.