How to create an SQL Server 2005 CTE to return par

Question

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.

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

Answer 2

This is the link I used to find a solution..

http://wiki.lessthandot.com/index.php/Using_Common_Table_Expressions_for_Parent-Child_Relationships

EDIT - @Pshimo - thanks. Heres the guide form the link.

With SQL 2005 though it is a dream. Say you have this sample data:

declare @test table (bunchof uniqueidentifier default newid(), columns uniqueidentifier default newid(), Id int, ParentID int)

insert @test (Id, ParentId)
select 1, null
union all select 5, 1
union all select 15, 2
union all select 16, 5
union all select 27, 16

And you want to get all child rows for 1 (so ItemId 5, 16, 27)

 declare @parentId int
    set @parentId = 1

    ;--last statement MUST be semicolon-terminated to use a CTE
    with CTE (bunchof, columns, Id, ParentId) as
    (
        select bunchof, columns, Id, ParentId
        from @test
        where ParentId = @parentId
        union all
        select a.bunchof, a.columns, a.Id, a.ParentId
        from @test as a
        inner join CTE as b on a.ParentId = b.Id
    )
    select * from CTE

and if you want to include the parent:

declare @Id int
set @Id = 1

;--last statement MUST be semicolon-terminated to use a CTE
with CTE (bunchof, columns, Id, ParentId) as
(
    select bunchof, columns, Id, ParentId
    from @test
    where Id = @Id
    union all
    select a.bunchof, a.columns, a.Id, a.ParentId
    from @test as a
    inner join CTE as b on a.ParentId = b.Id
)
select * from CTE

You can select depth in the hierarchy as well, if you're into that kind of thing:

declare @Id int
set @Id = 1

;--last statement MUST be semicolon-terminated to use a CTE
with CTE (bunchof, columns, Id, ParentId, Depth) as
(
    select bunchof, columns, Id, ParentId, 0
    from @test
    where Id = @Id
    union all
    select a.bunchof, a.columns, a.Id, a.ParentId, b.Depth + 1
    from @test as a
    inner join CTE as b on a.ParentId = b.Id
)
select * from CTE

As you can see what you're doing here is first selecting your initial recordset, which contains all child rows for your parent ID parameter. You can then union to another query that joins to the CTE itself, to get the children's children (and their grandchildren, and so forth until you reach the last descendant row. Its' important to note that the default recursion limit is 100, so pay attention to the depth of your hierarchy when using these. You can change the recursion limit using the OPTION (MAXRECURSION)

 WITH CTE AS (
    ...
    )
    SELECT * FROM CTE OPTION (MAXRECURSION 1000)