Parent/Child and Recursion
-
I have data in a table like this:
Parent Child
300001 110081
300001 102157
300001 102158
300001 102159
110081 101000
110081 101504
110081 102129
Child 110081 of parent 300001 has children 101000, 101504, and 102129.
I need a query that returns this result:
Parent Child
300001 101000
300001 101504
300001 102129
300001 102157
300001 102158
300001 102159
I've tried a recursive CTE without success. Table values are in varchar format. Help Please?
-
Try this
DECLARE @t TABLE(Parent VARCHAR(10), Child VARCHAR(10))
INSERT INTO @t(Parent,Child)
VALUES
('300001','110081'),
('300001','102157'),
('300001','102158'),
('300001','102159'),
('110081','101000'),
('110081','101504'),
('110081','102129');
WITH Recur AS (
SELECT Parent,Child
FROM @t t
WHERE NOT EXISTS(SELECT * FROM @t t2 WHERE t2.Child=t.Parent)
UNION ALL
SELECT r.Parent,t.Child
FROM Recur r
INNER JOIN @t t ON t.Parent=r.Child)
SELECT r.Parent,r.Child
FROM Recur r
WHERE NOT EXISTS(SELECT * FROM @t t WHERE t.Parent=r.Child)
ORDER BY r.Parent,r.Child;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Here is another variation:
DECLARE @t TABLE(Parent int, Child int)
INSERT INTO @t(Parent,Child)
VALUES
(300001,110081),
(300001,102157),
(300001,102158),
(300001,102159),
(110081,101000),
(110081,101504),
(110081,102129);
Declare @Root int
set @Root = 300001;
WITH Recur AS (
SELECT Parent,Child, 1 as Level
FROM @t
WHERE Parent = @Root
UNION ALL
SELECT r.Parent,t.Child, r.Level + 1
FROM Recur r INNER JOIN @t t ON t.Parent=r.Child)
SELECT @Root as RootParent, Child
FROM Recur
ORDER BY Level, Child;
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply