CODEWITHSUNDEEP
mysqlsql
Xakir K Moideen
Xakir K Moideen

Reputation: 157

How to get all parents from child id

I created a table with name "net" and inserted values just like this. 

id      name            parent
--------------------------------------
    1   fruits          0
    2   Apple           1
    3   Orange          1
    4   Grape           2
    5   Green Apple     2
    6   Red Apple       4

I want to get all parents of the child "6 ". please help me

The result i was expecting,when filtering the data ,where id=6,it will generate the result i'm expecting.

lv1         lv2             lv3
--------------------------------------
4             2               1

or like this

       id   name           parent
--------------------------------------
        4   Grape           2
        2   Apple           1
        1   fruits          0

Upvotes: 2

Views: 1274

Answers (1)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

You can use this :

SELECT T2.id, T2.name, T2.parent
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent FROM table1 WHERE id = _id) AS parent,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 6, @l := 0) vars,
        table1 h
    WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id 
where T2.id<>6
ORDER BY T1.lvl

SQL HERE

Upvotes: 3

Related Questions