Show data based on hierarchy [Grand Parent & # 8594; Parent & # 8594; Child]

Question

Hi, I want to show the data as a way of hierarchy, as shown in the image below.

and here is my database table structure

and here is the query that I used, but its not the perfect result that I want

SELECT t1.parent_id AS primary_Id, t2.parent_id AS secondary_Id, t3.parent_id AS teritiary_Id FROM ucode AS t1 LEFT JOIN ucode AS t2 ON t2.parent_id = t1.id LEFT JOIN ucode AS t3 ON t3.parent_id = t2.parent_id 

and the way out is

I do not need it

and another way that I tried

 SELECT t1.parent_id AS primary_Id, t2.parent_id AS secondary_Id, t3.parent_id AS teritiary_Id FROM ucode AS t1 LEFT JOIN ucode AS t2 ON t2.parent_id = t1.id LEFT JOIN ucode AS t3 ON t3.parent_id = t2.id 

and the way out is

Can you give me the right solution for this.

Answer 1

Here is the request

 Select gparent, parent, id from ucode as c_tbl, (SELECT gparent, id as parent from ucode as p_tbl, (SELECT id as gparent FROM `ucode` WHERE parent_id = 0) as gp_tbl where p_tbl.parent_id = gp_tbl.gparent) parent_tbl where c_tbl.parent_id = parent_tbl.parent; 

Click Demo for Result

Answer 2

You need to use INNER JOIN instead of LEFT JOIN and restrict the request to return only to parents using WHERE children

My query does not skip the column value as you would like to show, but you can do this with PHP, looping the result below.

 SELECT t1.parent_id AS primary_Id, t2.parent_id AS secondary_Id, t3.parent_id AS teritiary_Id FROM ucode AS t1 INNER JOIN ucode AS t2 ON (t2.parent_id = t1.id ) INNER JOIN ucode AS t3 ON (t3.parent_id = t2.id ) where t1.parent_id = 0 

Demo