SQL Database Parent/Child recursion

Question

Here is my table:

parent_id | child_id
--------------
        1 | 2
        1 | 3
        1 | 4
        2 | 5
        2 | 6
        5 | 8
        8 | 9
        9 | 5

I need to get all of the items under parent 2. I've found a few things similar to this, but but couldn't figure out how to make it work for my case. I keep getting maximum recursion limit reached. Here's what I have:

WITH CTE AS
(
    SELECT gt.[child_id]
      FROM [CHSPortal].[dbo].[company_adgroupstoadgroups] gt
     WHERE gt.parent_id='2'

    UNION ALL

    SELECT g.[child_id]
      FROM [CHSPortal].[dbo].[company_adgroupstoadgroups] g
      INNER JOIN CTE g2 on g.parent_id=g2.child_id
)
select distinct child_id from CTE

The desired result is going to be: 2,3,4,5,6,8,9.

What modification do I need to make to get a list of all the items under child 2. I would also prefer 2 (the parent node) to be in the list. Any help would be appreciated.

I was wondering if the sample data is real data? Because according to that, you would be implementing infinite recursion. All items under 2 = 5,6. Items under 5 = 8, Items under 8 = 9, Items under 9 = 5, then it goes back to 5,8,9 indefinitely. — Zack Z.
– Zack Z., Commented Dec 6, 2013 at 21:00
Why not just use one select statement and use a where clause: where parent_id = 2? Also you said you need all items under parent 2 and then you needed all items under child 2 later. What are you looking for? — the_pete
– the_pete, Commented Dec 6, 2013 at 21:02
@the_pete - The data in the table represents a 'tree', or other recursive structure, and the OP is looking to do Tree Traversal. Of course, this particular instance has the noted cyclical data problem. A simple SELECT statement is insufficient for this task - there is a specific syntax required for this type of work, as the existing answers are using. — Clockwork-Muse
– Clockwork-Muse, Commented Dec 7, 2013 at 12:05
I will loop infinitely, but because that is the way the data structure is set up. But it will not keep adding nodes. Once it goes back to a previous value, i don't need it to go any deeper. — Hoopdady
– Hoopdady, Commented Dec 7, 2013 at 22:01

TamasM · Accepted Answer · 2013-12-07 10:42:04Z

1

First of all, there is a loop in your example (5|8, 8|9, 9|5), that is why you reach the maximum recursion limit.

Regarding the filtering question,below you can find an example for filtering by root node:

;WITH MTree (parent_id, child_id, LEVEL) AS (
    SELECT t.parent_id , t.child_id, 0 AS LEVEL 
    FROM table_1 t
    WHERE child_id = 2 --here you can filter the root node
UNION ALL      
    SELECT m.parent_id , m.child_id, LEVEL + 1
    FROM Table_1 m
        INNER JOIN MTree t ON t.child_id = m.parent_id
)

SELECT * FROM Mtree;

edited Dec 7, 2013 at 10:42

answered Dec 7, 2013 at 10:24

TamasM

112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TamasM Over a year ago

if you would like to exclude the corresponding top row, the use the following filter in the CTE: WHERE parent_id = 2 --here you can filter the root node

Hart CO · Accepted Answer · 2013-12-06 21:28:55Z

0

Not sure what's wrong with your query, aside from not relating to the sample data you provided, but this works just fine:

;WITH src AS (SELECT 1 AS parent_id, 2 AS child_id
               UNION  SELECT 1, 3
               UNION  SELECT 1, 4
               UNION  SELECT 2, 5
               UNION  SELECT 2, 6
               UNION  SELECT 5, 8
               UNION  SELECT 8, 9
               UNION  SELECT 9, 5)
    ,cte AS (SELECT *
             FROM src
             WHERE child_id = 2
             UNION  ALL
             SELECT a.*
             FROM src a
             JOIN cte b
               ON a.parent_id = b.child_id           
            )
SELECT TOP 100 *
FROM cte

--Limited to top 100 because of infinite recursion problem with sample data.

edited Dec 6, 2013 at 21:28

answered Dec 6, 2013 at 21:15

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

2 Comments

Hoopdady Over a year ago

The limit does prevent the infinite recursion problem, but the actual data is much larger than 100 enties, and I don't want duplicates. How can I get a distinct list of the values without knowing how much data will return?

Hart CO Over a year ago

You can set a max recursion factor with OPTION (MAXRECURSION 0) adjusting the 0 to a number that makes sense, then add DISTINCT to the SELECT statement. However your data is the real problem, you could add a WHERE clause that prevents the infinite loop.

Collectives™ on Stack Overflow

SQL Database Parent/Child recursion

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related