Apply INNER JOIN only if there is a row available otherwise use NULL

id
value_id
parent_id
dropdown_id
name

7
14945
14944
57
4000

8
14944
0
56
bbb

9
14943
14940
59
comprable

10
14942
14939
59
comprable

11
14940
14931
58
3

12
14939
14930
58
2

13
14931
14929
57
5000

14
14930
14928
57
4000

15
14929
0
56
rrr

16
14928
0
56
ttt

I have above table with data. I an using below query to get data from the table.

SELECT `d0`.`name` AS `name0`,
       `d1`.`name` AS `name1`,
       `d2`.`name` AS `name2`,
       `d3`.`name` AS `name3`
FROM   `my_table_1` AS `d0`
       INNER JOIN `my_table_1` AS `d1`
               ON d1.parent_id = d0.value_id
       INNER JOIN `my_table_1` AS `d2`
               ON d2.parent_id = d1.value_id
       INNER JOIN `my_table_1` AS `d3`
               ON d3.parent_id = d2.value_id
WHERE  ( d0.dropdown_id = 56 )

Here I am using inner join to get value_id from its parent_id.

Basically, it will check if there is a parent available for current record and will get its data if any. I can get correct data if I have as number of parents for a record as the number of INNER JOIN

Like with above query I get details like

name0
name1
name2
name3

ttt
4000
2
comprable

rrr
5000
3
comprable

Here I want to get below table

name0
name1
name2
name3

ttt
4000
2
comprable

rrr
5000
3
comprable

bbb
4000
NULL
NULL

Here in last row, I do not have d2 and d3 tables available. I want to include that data too with NULL value.

Any help is appreciated.

If you replace the inner joins with left joins you will get the data that you want. The left join shows the results even with there is not a match on the other side and in those cases shows a null.

SELECT `d0`.`name` AS `name0`,
       `d1`.`name` AS `name1`,
       `d2`.`name` AS `name2`,
       `d3`.`name` AS `name3`
FROM   `my_table_1` AS `d0`
       LEFT JOIN `my_table_1` AS `d1`
              ON d1.parent_id = d0.value_id
       LEFT JOIN `my_table_1` AS `d2`
              ON d2.parent_id = d1.value_id
       LEFT JOIN `my_table_1` AS `d3`
              ON d3.parent_id = d2.value_id
WHERE  ( d0.dropdown_id = 56 )

StackExchange Database Administrators Q#180006, answer score: 19