Use SQL CTE table to include path and all children

Question

I have the following tree hierarchical table

GO DROP TABLE #tbl GO CREATE TABLE #tbl (Id int , ParentId int) INSERT INTO #tbl (Id, ParentId) VALUES (0, NULL) INSERT INTO #tbl (Id, ParentId) VALUES (1, 0) INSERT INTO #tbl (Id, ParentId) VALUES (2, 1) INSERT INTO #tbl (Id, ParentId) VALUES (3, 1) INSERT INTO #tbl (Id, ParentId) VALUES (4, 2) INSERT INTO #tbl (Id, ParentId) VALUES (5, 2) INSERT INTO #tbl (Id, ParentId) VALUES (6, 3) INSERT INTO #tbl (Id, ParentId) VALUES (7, 3) GO 

Which corresponds to the following tree

 0 +- 1 +- 2 +- 4 +- 5 +- 3 +- 6 +- 7 

Using the CTE recursive table, how can I get the path, as well as all the children of the selected node. For example, having 2 as input, how can I get the following data (if possible, arrange)

 Id, ParentID ------- 0, NULL 1, 0 2, 1 4, 2 5, 2 

I know that I can walk through the tree (get the path) with the following instruction

 WITH RecursiveTree AS ( -- Anchor SELECT * FROM #tbl WHERE Id = 2 UNION ALL -- Recursive Member SELECT Parent.* FROM #tbl AS Parent JOIN RecursiveTree AS Child ON Child.ParentId = Parent.Id ) SELECT * FROM RecursiveTree 

And with the following statement, cross in the tree (get all the children)

 WITH RecursiveTree AS ( -- Anchor SELECT * FROM #tbl WHERE Id = 2 UNION ALL -- Recursive Member SELECT Child.* FROM #tbl AS Child JOIN RecursiveTree AS Parent ON Child.ParentId = Parent.id ) SELECT * FROM RecursiveTree 

Question: How to combine these two teams into one?

Answer 1

Just use the UNION of these two elements

SQLFiddle demo

 WITH RecursiveTree AS ( -- Anchor SELECT * FROM #tbl WHERE Id = 2 UNION ALL -- Recursive Member SELECT Parent.* FROM #tbl AS Parent JOIN RecursiveTree AS Child ON Child.ParentId = Parent.Id ), RecursiveTree2 AS ( -- Anchor SELECT * FROM #tbl WHERE Id = 2 UNION ALL -- Recursive Member SELECT Child.* FROM #tbl AS Child JOIN RecursiveTree2 AS Parent ON Child.ParentId = Parent.id ) select * from ( SELECT * FROM RecursiveTree union SELECT * FROM RecursiveTree2 ) t order by id