October 10, 2015 at 8:06 am
Hello,
i have below queries each select is fetching records at one level. Is there a way i can write single query to get to nth level (recursion) instead joining same table 10 times (i don't know in some cases there is may be next level) I stopped at 10th level now. In below example i gave only two levels.
SELECT Distinct
a.Col1 AS EmpID,
a.Col1 AS EmpID,
a.Col2 AS Emp_guid,
a.Col2 AS Emp_guid,
case
when a.col3 = 1 then 'Y'
when a.col3 = 1 then 'N'
else Null
end AS Status
FROM TableA a
SELECT Distinct
b.Col1 AS EmpID,
a.Col1 AS EmpID,
b.Col2 AS Emp_guid,
a.Col2 AS Emp_guid,
case
when b.col3 = 1 then 'Y'
when b.col3 = 1 then 'N'
else Null
end AS Status
FROM TableA a
join Tableb b
on a.col4 =b.col1
SELECT Distinct
b.Col1 AS EmpID,
a.Col1 AS EmpID,
b.Col2 AS Emp_guid,
a.Col2 AS Emp_guid,
case
when b.col3 = 1 then 'Y'
when b.col3 = 1 then 'N'
else Null
end AS Status
FROM TableA a
join Tableb lev
on a.col4 = lev.col1
join Tableb b
on lev.col4 =b.col1
October 10, 2015 at 10:44 am
There are a couple ways of doing this. The simplest method is to use a recursive CTE.
Something like the following...
-- Create some test date...
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
ChildID INT,
ParentID INT,
ChildName VARCHAR(50)
);
INSERT #temp (ChildID, ParentID, ChildName) VALUES
(1, NULL, 'Aaron'), (2, 1, 'Bob'), (3, 1, 'Clay'), (4, 1, 'Darrin'), (5, 2, 'Edward'), (6, 2, 'Frank'), (7, 2, 'Gail'),
(8, 3, 'Hugo'), (9, 3, 'Izzy'), (10, 3, 'Jason'), (11, 3, 'Karl'), (12, 4, 'Lary'), (13, 4, 'Mark'), (14, 4, 'Nancy'),
(15, 4, 'Osmund'), (16, 4, 'Perry'), (17, 5, 'Quincy'), (18, 5, 'Ray'), (19, 5, 'Sam'), (20, 5, 'Toni'), (21, 6, 'Ulrick'),
(22, 6, 'Vinny'), (23, 6, 'Wesley'), (24, 6, 'Xavier'), (25, 6, 'Yancy'), (26, 7, 'Zoe'), (27, 7, 'Axel'), (28, 7, 'Ben'),
(29, 8, 'Craig'), (30, 8, 'Don'), (31, 9, 'Eddy'), (32, 9, 'Fry'), (33, 9, 'Greg'), (34, 10, 'Heidi'), (35, 10, 'Ilean'),
(36, 10, 'Joe'), (37, 10, 'Kevin'), (38, 10, 'Lou');
-- Recursive CTE method
WITH Hierarchy AS (
SELECT-- Define the anchor node.
t.ChildID,
t.ParentID,
t.ChildName,
0 AS NodeLevel
FROM
#temp t
WHERE
t.ParentID IS NULL
UNION ALL
SELECT-- Add the recursive nodes.
t.ChildID,
t.ParentID,
t.ChildName,
h.NodeLevel + 1 AS NodeLevel
FROM
#temp t
JOIN Hierarchy h
ON t.ParentID = h.ChildID
)
SELECT
h.ChildID,
h.ParentID,
h.ChildName,
h.NodeLevel
FROM
Hierarchy h
ORDER BY
h.NodeLevel,
h.ParentID,
h.ChildID
--OPTION (MAXRECURSION 200)-- Only needed if there is a possibilty that you go more than 100 levels deep
;
If you need better performance, do some research on the "Nested Sets Model". Joe Celko has written extensively on the topic and Jeff Moden has an awesome article here on SSC[/url] that includes, what is to the best of my knowledge, the fastest method for populating the Left & Right bowers that are needed for the model.
HTH,
Jason
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply