Mysql – While Loop – Parent/Child Tree

hierarchyMySQLrecursive

I'm trying to recursively loop through and return all child_id's that have the root element of 9.

The structure:

->9
->->8
->->->17
->->22
->->->11

Parent Child Link Table:

+----+-----------+----------+
| id | parent_id | child_id |
+----+-----------+----------+
|  1 |         9 |        8 |
|  2 |         8 |       17 |
|  3 |         8 |       33 |
|  4 |         8 |       18 |
|  5 |         9 |       22 |
|  6 |        22 |       11 |
|  7 |        22 |        4 |
|  8 |         3 |        5 |
+----+-----------+----------+

Procedure (so far):

BEGIN

DECLARE x INT(11)

SET x = 0;
SET @elements = "";
SET @node = _root_; -- 9
SET @child_count = count_children(@node) -- function returning the child count of @node;
SET @children = get_children(@node); -- function returning the child id's of @node

-- check IF node has children
WHILE x <= @child_count DO
    SET @elements = CONCAT(@elements,x,',');
    SET x = x + 1;
END WHILE
SELECT @elements;

END

Desired Output: [8,17,33,18,22,11,4]

Question: How can I modify my procedure to be able to return all child_id's of the parent?

Best Answer

try this,

declare @t table( id int, parent_id int,child_id int)
insert into @t values
(1 , 9 ,  8 ),(2 , 8 , 17 )
,(3 , 8 , 33 ),(4 , 8 , 18 )
,(5 , 9 , 22 ),(6 ,22 , 11 )
,(7 ,22 ,  4 ),(8 , 3 ,  5 )

declare @parentinput int=9

select t.* from @t t
left join @t t1
on t.child_id=t1.parent_id
where t.parent_id=@parentinput

union 
select t1.* from @t t
left join @t t1
on t.child_id=t1.parent_id
where t.parent_id=@parentinput
Related Question