Menu
I have following hierarchical tree 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 maps to following tree
0
+- 1
+- 2
+- 4
+- 5
+- 3
+- 6
+- 7
Using CTE Recursive table, how can I get the path and also all children of the selected node. For example having 2 as input, how can I get following data (ordered if possible)
Id, ParentID
-------
0, NULL
1, 0
2, 1
4, 2
5, 2
I know I can traverse up in the tree (get path) with following statement
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 following statement, traverse down in the tree (get all 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 commands into one?
733
Unlike a derived table, a CTE behaves more like an in-line view and can be referenced multiple times in the same query. Using a CTE makes complex queries easier to read and maintain. Because a CTE can be referred to multiple times in a query, syntax can be simpler.
Disadvantages of CTECTE's members cannot use the following clauses of keywords Distinct, Group By, Having, Top, Joins limiting by this type of the queries that can be created and reducing their complexity. The Recursive member can refer to the CTE only once.
As others mentioned, it just depends on the query. Moving the WHERE clause outside vs inside of a CTE can alter the execution plan and result in drastically different performance. I'm seen it happen numerous times. But in simple queries, generally it'll make no difference.
A common table expression, or CTE, is a temporary named result set created from a simple SELECT statement that can be used in a subsequent SELECT statement. Each SQL CTE is like a named query, whose result is stored in a virtual table (a CTE) to be referenced later in the main query.
Just use UNION of these two selects
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
