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

Question

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.

Answer 1

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 ) 

Answer 2

Use a Left Join instead of an Inner Join.

An Inner Join will only give you rows that match from both the left and right table.

A Left Join will return all records from the left table and matching records from the right table. Where there are no matches from the right table, NULLs will appear in place of the right table columns, instead.