CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
Bill Gregg
Bill Gregg

Reputation: 7147

SQL To Find All Descendents

I have a data table like this

 Entities:
 ID | Parent_ID
 1  | null
 2  | 1
 3  | 1
 4  | 3
 5  | 4
 6  | 4

I'd like a sql expression that will return a row for every entity and a linear descendant, plus a row for null if the entity has no descendants. So given the above data my result set would be:

Entity | Descendant
1      | 2
1      | 3
1      | 4
1      | 5
1      | 6
2      | null
3      | 4
3      | 5
3      | 6
4      | 5
5      | null
6      | null

I tried using a common table expression to achieve this, and think it's the way to do it, given its ability to recurse, but I couldn't get my head wrapped around the spawning of many rows for a single parent.

with all_my_children (my_father,my_id,my_descendant,level)
as
(   
    select parent_id,id,null,0
    from Entities
    where id not in (select parent_id from entities)
    union all
    select e.parent_id,e.id,amc.my_id,amc.level+1 
    from Entities e
    inner join all_my_children amc 
    on e.id = amc.my_father
    WHERE ????? --How do I know when I'm done? and How do I keep repeating parents for each descendant?

)

select my_id, my_descendant from all_my_children

Thanks for your time.

Upvotes: 1

Views: 524

Answers (2)

Santhosh
Santhosh

Reputation: 1791

Here's what you asked for 

WITH TEMP AS
(

    SELECT ID AS ENTITY, PID AS DESCENDANTS
    FROM YPC_BI_TEMP.DBO.SV7104
    WHERE PID IS NULL
    UNION ALL
    SELECT PID AS ENTITY, ID AS DESCENDANTS
    FROM YPC_BI_TEMP.DBO.SV7104
    WHERE PID IS NOT NULL
    UNION ALL
    SELECT PRNT.ENTITY, CHILD.ID  AS DESCENDANTS
    FROM YPC_BI_TEMP.DBO.SV7104 AS CHILD
    INNER JOIN TEMP AS PRNT
    on PRNT.DESCENDANTS = CHILD.PID
    --AND PRNT.ENTITY IS NOT NULL

)
SELECT DISTINCT ENTITY, DESCENDANTS FROM TEMP
UNION
SELECT ID AS ENTITY, NULL AS DESCENDANTS FROM YPC_BI_TEMP.DBO.SV7104
WHERE ID NOT IN (SELECT ENTITY FROM TEMP)

Upvotes: 3

pwnyexpress
pwnyexpress

Reputation: 1016

Deleted my previous answer, but I think this might do the trick...

WITH all_my_children AS (my_father,my_id,my_descendant,level)
        (
        SELECT  parent_id, id, null, 0
        FROM    Entities
        WHERE   parent_id IS NULL -- the roots of your tree
        UNION ALL
        SELECT  COALESCE(e2.parent_id, e.parent_id), e.id, amc.my_id, amc.level+1
        FROM    Entities e
        JOIN    all_my_children amc
        ON      e.parent_id = amc.my_id
        LEFT JOIN    Entities e2
        ON      e.id = e2.parent_id
        )
SELECT  * FROM  all_my_children

Upvotes: 2

Related Questions