0

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?

  • So is it just that, you want to return an array of the child_id column? – Nelz Jun 5 '17 at 23:39
  • @NelsonCasanova only child elements who have the parent node of 9. – Jordan Davis Jun 6 '17 at 3:00
  • If you are using version 8.0 or 10.2, there are "recursive CTEs" to do tasks like this. Otherwise, you need to write a look in your app code (or in a Stored Proc) to do the task. – Rick James Jun 6 '17 at 17:43
  • @RickJames yeah I've been looking at those, dba.stackexchange.com/questions/7147/… – Jordan Davis Jun 6 '17 at 17:54
0

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
  • If I'm correct, this works with the data as given, but only allows for three levels: parent, child, and grandchild. No specific limit on the number of levels deep the parent-child relationships can go was specified, so I'm not sure that meets the OP's needs. – RDFozz Jun 6 '17 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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