How to make multiple joins?

Analytics
Enthusiast

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

2 REPLIES
Senior Apprentice

Re: How to make multiple joins?

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
Enthusiast

Re: How to make multiple joins?

Dieter, Thanks again.