MySQL – Apply INNER JOIN Only if Row Available, Otherwise Use NULL

join;MySQL

+----+-----------+-----------+-------------+----------+
| 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.

Best Answer

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)