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

Question

I am experimenting with CTE in SQL Server, but have come to a standstill by getting the following script to work. I have a hierarchy table like 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, we have a parent-child hierarchy table with one subtle difference. Each child can potentially have more than one parent. I researched many blog articles and StackOverflow posts about creating CTEs that return parent records, but they do not return all parents for children, only the first one he finds.

Here is an example of a 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 table and column names in the above CTE have been changed from the original for privacy purposes.)

The above CTE works fine, it finds all parent records starting with ID: 439, but it finds only one parent for item id: 900, although it has two parents.

Can someone tell me if this is possible using CTE, or is there another SQL way to do this?

Greetings. Jac.

Answer 1

This seems to work fine for me as soon as I fixed the syntax error in the 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 the solution.

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

EDIT - @Pshimo - thanks. Heres the guide forms a link.

Since SQL 2005, although this is a dream. Say you have data from this sample:

 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 the 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 also choose the depth in the hierarchy if you do these things:

 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 are doing here, first select your initial recordset containing all the child lines for the parent id parameter. Then you can combine in another query that joins the CTE itself to get the children-children (and their grandchildren, etc., until you reach the last line of the descendant. It is important to note that the default recursion limit is 100, so pay attention to the depth of your hierarchy when using them.You can change the recursion limit with OPTION (MAXRECURSION)

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