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

0

2 Answers 2

19

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

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.

1
  • Using outer joins doesn't quite solve this issue. A null from the table that may not have a row in it only tells you that none of its rows match.
    – roblem
    Apr 12, 2021 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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