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

  • 5
    Sounds like you are wanting to use a left join to include all of the data. – Joe W Jul 17 '17 at 12:58
  • @JoeW this should be an answer IMO not a comment. It's the right one unless OP has some need for a COALESCE/FULL JOIN and getting data from joined tables even though it doesn't exist in the primary table. – MguerraTorres Jul 17 '17 at 13:00
  • 'Here I am using inner join to get value_id from its parent_id.' That doesn't look quite like what you are doing? Looks a bit messy to me – Mark Sinkinson Jul 17 '17 at 13:02
7

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)
4

Use Left Joins, here's why:

The Basics of Joins

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. This means that where there are no matches from the right table, NULLs will appear in place of the right table columns.

A Right Join acts the same but reverse behavior for left and right tables.

Then there are Full Outer Joins and Cross Joins but you can look that up as they don't apply to your question!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.