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 https://stackoverflow.com/questions/8104187/hierarchical-queries-in-mysql

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

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

But it is not working. Here is the SQLFiddle

Need help in making the query work.

  • The STRAIGHT_JOIN is a "cross join" in your case. Why do you think you need that?
    – Rick James
    Jan 15, 2016 at 19:27
  • I don't know exactly what the query does. Just copied it from another solution, modified it to adapt to my scenario. It works, if a parent has only one child, but fails in one to many relation, as the sub query in that case has more than one row
    – Ehs4n
    Jan 15, 2016 at 19:34
  • I would (and have done) write application code to traverse the tree as needed. It's only a few SELECTs to go up to the root or down to the descendants.
    – Rick James
    Jan 15, 2016 at 20:01

2 Answers 2


The query in the linked answer is a "hack" as the OP there clearly describes in a comment and can stop working any time with a MySQL upgrade as the exact behaviour of session variables is not documented.

Aside from that, the question there was about finding all ancestors, not all descendants. In adjacency lists, a node has maximum one parent but possibly many children. So this query/hack will not work in your case.

The Adjacency List model is the simplest of all hierarchy models in SQL. The other 3 (Nested Sets, Evaluated Path, Closure Table) are more complicated because - in a way - store redundant information. In exchange, some queries are far more easier to write. In any of the other 3 models, the solution would be really simple and wouldn't need a recursive query.

So, it seems you have two options:

  • either evaluate your decision to use the Adjacency List model and use a different one (or a combination of models)
  • write a procedure / function that solves the issue. @RolandoDBA has an old answer on this site, that provides several procedures that solve this (and related) problems. I'm not sure how efficient they are but they surely work:
    Find highest level of a hierarchical field: with vs without CTEs.

You'll find more useful information in the links:

  • Creating custom function as mentioned by RolandoMySQLDBA worked. I have mentioned below the answer.
    – Ehs4n
    Jan 17, 2016 at 7:11

Finally, I created a function as suggested by @RolandoDBA at Find highest level of a hierarchical field: with vs without CTEs


CREATE FUNCTION `siblings` (GivenID INT) RETURNS varchar(1024) CHARSET latin1

    DECLARE rv,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';
    SET queue = GivenID;
    SET queue_length = 1;

    WHILE queue_length > 0 DO
        SET front_id = FORMAT(queue,0);
        IF queue_length = 1 THEN
            SET queue = '';
            SET pos = LOCATE(',',queue) + 1;
            SET q = SUBSTR(queue,pos);
            SET queue = q;
        END IF;
        SET queue_length = queue_length - 1;

        SELECT IFNULL(qc,'') INTO queue_children
        FROM `users` WHERE parent_id = front_id) A;

        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
            IF LENGTH(rv) = 0 THEN
                SET rv = queue_children;
                SET rv = CONCAT(rv,',',queue_children);
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;

    RETURN rv;

END $$

And then for getting siblings / children of a parent, a simple call using the above created function, siblings is enough.

SELECT siblings(id) AS `siblings` from `users` where `id` = 1

This returns result as


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.