Show the data based on Hierarchy [ Grand Parent -> Parent -> Child ]

Question

Hi I want to show data as Hierarchy way like this as show in below image

and here is my database table structure

and here is the query i have used but its not the perfect result 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 output is

this is not i need

and one more way 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 output is

can you give me the proper solution for this..

Answer 1

Here is the query

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 the result