Multi level hierarchy relation in flat table design (MySql)

0

My table design is

users (id, username..., parent_id)

Data is

id    username      parent_id
-------------------------------
1  |  admin       | null
2  |  reseller 1  | 1
3  |  client 1    | 1
4  |  reseller 2  | 1
5  |  reseller 3  | 2
6  |  reseller 4  | 2
7  |  client 2    | 5
8  |  client 3    | 6

I want to get all descendants of id 1

I studied Adjacency List, Nested List & Closure table design, but came to conclusion that Session based Adjacency List can be better in my situation.

I found an example at Hierarchical queries in MySQL

SELECT  @id :=
        (
        SELECT  senderid
        FROM    mytable
        WHERE   receiverid = @id
        ) AS person
FROM    (
        SELECT  @id := 5
        ) vars
STRAIGHT_JOIN
        mytable
WHERE   @id IS NOT NULL

I tried to modify this query to get my result like this

SELECT  group_concat(@id :=
        (
        SELECT  id
        FROM    users
        WHERE   parent_id = @id
        )) AS u
FROM    (
        SELECT  @id := 1
        ) vars
STRAIGHT_JOIN
        users
WHERE   @id IS NOT NULL

But it is not working. Here is the SQLFiddle

Need help in making the query work.

Link: Multi level hierarchy relation in flat table design (MySql)
Source: Stack Sql

Share.

About Author

Leave A Reply