Many-to-many to recursive CTE in SQL Server

Question

As I understand it, any many to many relationship is a hierarchy if you define one part as parent and another as child.

I have a situation where I need to get the children of an object that is a mapped to itself in a many to many table, but I am having a really hard time writing a recursive CTE for it.

For example, lets say I have:

ParentId           ChildId
========           =======
2                  20
2                  30
5                  50
20                 200
21                 201
30                 300
31                 301

For the root level, the rule I was using was that it is a root object, if its ID is not in second column (ChildId), but this is problematic because, as you can see from example above, I can have multiple records for root (Id: 2)

For the recursion part, no matter what I tried I either got infinite recursion or the recursion did not work at all.

So I am completely stuck.

UPDATE Just adding more clarification. I am building a stored procedure which gets passed in a parentId and which has the recursive CTE inside. The output I am trying to get is, for passed in parentId of 2, using the sample above, I would like:

ParentId           ChildId           Level
========           =======           =====
2                  20                0
2                  30                0
20                 200               1
30                 300               1

Here is some of my code:

alter procedure [dbo].[FindChildren]
    @Id bigint
as
begin

    ;with DocumentHierarchyCTE (ParentId, ChildId, LevelNumber)
    as
    (
        select 
            th.ParentDocumentId as ParentId,        
            th.ChildDocumentId as ChildId,
            0 as LevelNumber            
        from dbo.DocumentHierarchy th 
        where                       
            th.ParentDocumentId = @Id
            and not exists(select 1 from dbo.DocumentHierarchy tz where tz.ChildDocumentId = th.ParentDocumentId )
        union all
        ???
    )
    select *    
    from DocumentHierarchyCTE d
    option (maxrecursion 0)
end
go

Hi and welcome to SO. You say the recursion did not work at all. What does that mean? Can you share what you tried? Also, your sample data is a bit confusing (and seems incomplete). This would be a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Aug 23 '18 at 14:09
Why is sample data confusing? 2 has children (20,30), 20 has children(200), 30 has children(300). When I was saying recursion did not work, I mean the recursion did not return anything but the root record. — ColdAir, Aug 23 '18 at 14:20
Well...where are 200 and 300? And how do you know that 2 is your root? The idea of a recursive cte is to start at the root and find all the children. You don't have a way in this data to know what the root is. Again, what did you try? By that I mean share the query you tried that didn't work. Give us something to work with. — Sean Lange, Aug 23 '18 at 14:32
@Sean Lange: 2 is one of the roots because it is not in ChildId column. 200 and 300 are leaf children. — ColdAir, Aug 23 '18 at 14:37
Oh...that is kind of ugly but I see. A big part of the issue here is that your data is not normalized which makes querying a lot more painful. Or is this a junction table? Also, do you just ignore the problem of missing children rows? — Sean Lange, Aug 23 '18 at 14:41
You may call it a junction table. I like to think of it as many-to-many relationship table. I am not sure what you mean by missing children rows. — ColdAir, Aug 23 '18 at 14:43
So this is the table that instead of many to many creates two different 1:many relationships? That does not really mimic a hierarchy. By missing children I mean things like rows that have a ChildID but that isn't in this table. — Sean Lange, Aug 23 '18 at 14:46
"So this is the table that instead of many to many creates two different 1:many relationships?" Yes, but isn't two 1:many a many-to-many? I think of it as a many-to-many but with sides defined as parent and child. "By missing children I mean things like rows that have a ChildID but that isn't in this table" Still dont know what you mean. This table defines the hierarchy, if something is not in this table, its not in the hierarchy and can be ignored. — ColdAir, Aug 23 '18 at 14:50

score 0 · Accepted Answer · answered Aug 23 '18 at 14:51

This is just a typical recursive cte of which there are thousands of examples all over the place. This returns what I think you want.

declare @Something table
(
    ParentId int
    , ChildId int
)
insert @Something values
(2, 20)
, (2, 30)
, (5, 50)
, (20, 200)
, (21, 201)
, (30, 300)
, (31, 301)

declare @ParentID int = 2
;

with MyCTE as
(
    select *, MyLevel = 0
    from @Something s
    where s.ParentId = @ParentID
    AND ParentID not in
    (
        Select s2.ChildId
        from @Something s2
    )
    union all
    select s3.*, c.MyLevel + 1
    from @Something s3
    join MyCTE c on c.ChildId = s3.ParentId
)

select *
from MyCTE

Wow, it was that simple :) I was over complicating it. Thanks — ColdAir, Aug 23 '18 at 14:57

score 0 · Answer 2 · answered Aug 23 '18 at 15:00

Here is basically the same answer as Sean Lange. But since I had this ready to go, figured I'd post it anyway.

;WITH list_structure AS
(
SELECT
    l.parentID,
    l.childID,
    0 AS levels
    FROM list_hierarchy l
    WHERE l.parentID NOT IN (SELECT childID FROM list_hierarchy)
UNION ALL
SELECT        
    l.parentID,
    l.childID,
    levels+1
    FROM list_hierarchy l
    INNER JOIN list_structure
    ON l.parentID = list_structure.childID
)
SELECT * FROM list_structure

Many-to-many to recursive CTE in SQL Server

2 Answers2