Hierarchical data - Get all children by parent in mysql query

Questions

I have a table with userid, managerid as follows:

id       manager
-------  -------
admin    (NULL)       
james    admin  
user     james  
workad   user   
creator  workad 

Now I want all the children (descendants) for one user id. In other words, for the userid james i need the children user, workad, creator . Because james is the top parent (ancestor). Is there any query to fetch result like this in mysql... Thanks in advance.

Answers

For that you need to have a stored function :

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetFamilyTree` $$
CREATE FUNCTION `GetFamilyTree` (GivenID  VARCHAR(1024)) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN

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

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

    WHILE queue_length > 0 DO
        SET front_id = queue;
        IF queue_length = 1 THEN
            SET queue =   ;
        ELSE
            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 (SELECT GROUP_CONCAT(id) qc
        FROM Table1 WHERE manager = front_id) A;

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

    RETURN rv;
END $$

Then you can call like :

SELECT `id`, `manager`,GetFamilyTree(`id`) as children 
from Table1;

You can have filters as well:

SELECT `id`, `manager`,GetFamilyTree(`id`) as children 
from Table1 where `id` =  james ;

http://www.sqlfiddle.com/#!2/0058f/3 http://www.sqlfiddle.com/#!2/0058f/3

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/19091457/get-all-children-by-parent-in-mysql-query

Related