2

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
Xakir K Moideen
  • 157
  • 9
  • You can achieve this by using CTE (Common Table Expression) which is the tool to implement recursion. [here](http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/) is an example that may be useful for you. – FDavidov Nov 07 '17 at 14:24
  • I don't think you can do this in a single query (unless, perhaps, you have a fixed -- and low -- limit on the tree depth). You could perhaps use a stored procedure to return the table you want, as described [here](https://stackoverflow.com/a/13251811/535871). Also, see [this article](http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) on ways of storing hierarchical data in MySQL. You might find the nested set model a better fit to your requirements than your current table design. – Ted Hopp Nov 07 '17 at 14:25

1 Answers1

3

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

Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43