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