[SOLVED] left join to get every left table row column and where possible matching row columns from two other tables

left join to get every left table row column and where possible matching row columns from two other tables

Table a:

id
1
2
3

Table b:

id  aid
11  1
12  2

Table c:

id  bid
21  11
22  12

SELECT *
FROM
    a
    LEFT JOIN
    b
    ON a.id = b.aid
    INNER JOIN c
    ON b.id = c.bid

How do I get every a and where possible the b and c?

a.id    b.id    b.aid   c.id    c.bid
1       11      1       21      11
2       12      2       22      12
3       null    null    null    null

The INNER JOIN is an extension of b when the prior LEFT JOIN matched.

Instead, I get:

a.id    b.id    b.aid   c.id    c.bid
1       11      1       21      11
2       12      2       22      12

Without the second INNER JOIN I get, as expected:

a.id    b.id    b.aid
1       11      1
2       12      2
3       null    null

Solution

This is the correct behaviour and SQL Server will return exactly the same result. See an online example here: http://rextester.com/EEGBZ41105

Because you are doing an inner join between b and c, this essentially voids the outer join between a and b.

If you look at the last row of the expected output:

a.id    b.id    b.aid   c.id    c.bid
3       null    null    null    null

And now look at the join condition b.id = c.bid it's pretty clear that it will remove that row because the value of b.id is null due to the outer join between a and b and thus the inner join removes that row again.

You have to use an outer join for the join between b and c as well:

SELECT *
FROM a
  LEFT JOIN b ON a.id = b.aid
  LEFT JOIN c ON b.id = c.bid
;