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

common-table-expressionparentssql-serversql-server-2005

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 Hierarchy(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.

Best Solution

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
Related Question