Left join inside left join

Question

I have problem getting values from tables.

I need something like this

A.Id a1
  B.Id b1
     C.Id c1
  B.Id b2
     C.Id c2
     C.Id c3
     C.Id c4

Table A and B are joined together and also table B and C. Table A can have one/zero or more values from table B. Same situation is for values from table C.

I need to perform left join on table A over table B and inside that left join on table B over table C.

I tried with left join from table A and B, but don't know how to perform left join inside that left join.

Is that possible? What would syntax for that look like?

edit: Data would look like this

ZZN1  P1  NULL
ZZN1  P2  NAB1
ZZN2  P3  NAB2
ZZN2  P3  NAB3

Answer 1

No need to nest the left joins, you can simply flatten them and let your RDMBS handle the logic.

Sample schema:

a(id)
b(id, aid) -- aid is a foreign key to a(id)
c(id, bid) -- bid is a foreign key to b(id)

Query:

select a.id, b.id, c.id
from a
left join b on b.aid = a.id
left join c on c.bid = b.id

If the first left join does not succeed, then the second one cannot be performed either, since joining column b.id will be null. On the other hand, if the first left join succeeds, then the second one may, or may not succeed, depending if the relevant bid is available in c.