How to create an SQL Server 2005 CTE to return parent-child records, for children with multiple parents.

Question

Hi there.

I'm experimenting with CTE's in SQL Server but have reached a dead end with getting the following scenario to work. I have a hierarchy table similar to this:

Node(ID:439)
  Node(ID:123)
    Node(ID:900)        
  Node(ID:56)
    Node(ID:900)

Expected results:

NodeID ParentNodeID
439    0
123    439
900    123
56     439
900    56

So basically we have a parent-child hierarchy table, with one subtle difference. Each child could potentially have more then one parent. I have researched many blog articles, and StackOverflow posts, about creating CTE's that return parent-child records, but they don't return all of the parents for the children, just the first one that it finds.

Here's an example CTE that I tried:

WITH Heirarchy(NodeID, ParentNodeID)
AS
(
    SELECT 
        T1.NodeID,
          T1.ParentNodeID
    FROM
        ParentChildTable T1
    WHERE
        T1.NodeID = 439

    UNION ALL
    SELECT 
        T1.NodeID,
        T1.ParentNodeID
    FROM
        Heirarchy T1
        INNER JOIN Heirarchy TH ON TH.NodeID = T1.ParentNodeID
)

(Note: The names of the tables and columns in the above CTE have been changed from the orginal for privacy purposes.)

The above CTE works fine, it finds all the parent-child records starting from ID:439, but it only finds one parent for item ID:900, even though it has two parents.

Could someone let me know if this is possible using CTE's, or is there another SQL way to do this?

Cheers. Jas.

Answer 1

This appears to work OK for me, once I corrected the syntax error in your CTE:

create table #ParentChildTable 
(nodeID int not null
,parentNodeID int not null
)

insert #ParentChildTable 
select 900,56
union all select 900,123
union all select 123,439
union all select 56,439
union all select 439,0

;WITH Heirarchy
AS
(
    SELECT 
        T1.NodeID,
          T1.ParentNodeID
    FROM
        #ParentChildTable T1
    WHERE
        T1.NodeID = 439

    UNION ALL
    SELECT 
        T1.NodeID,
        T1.ParentNodeID
    FROM
        #ParentChildTable T1
        INNER JOIN Heirarchy TH ON TH.NodeID = T1.ParentNodeID
)
select *
from Heirarchy

Returns the result:

NodeID      ParentNodeID
----------- ------------
439         0
123         439
56          439
900         56
900         123