July 18, 2016 at 1:03 pm
Hello All
Need a tsql query in sql server 2012 for the below.
We have a table with parent_id and child_id.Need to identify the level of the parent like below,
Parent_ID|Child_ID
A B
C D
B C
B E
E F
The output should be like below
Parent_ID|Child_ID|Level
A B 1 -> A does not have any parent so 1
C D 1 -> C does not have any parent so 1
B C 2 -> B does have any parent A so 2
B E 2 -> B does have any parent A so 2
E F 3 -> E does have any parent B,A so 3
Thanks in advance
July 18, 2016 at 1:19 pm
July 18, 2016 at 1:30 pm
Thank you for the quick reply. Yes B is the parent of C. The level column should populate in a view/proc dynamically. I achieved using multiple queries for each level, thought of knowing for the best method.
July 18, 2016 at 2:05 pm
Here are the 2 options I mentioned earlier. The recursive CTE has the advantage of returning the results in a single query. The set-based loop (in my experience) is usually faster. Read the article I linked in my previous post and test the queries. These queries are not validating against circular references and you need to prevent them.
CREATE TABLE #Sample(
Parent_ID char(1),
Child_ID char(1),
[Level] int
);
INSERT INTO #Sample(Parent_ID, Child_ID)
VALUES
('A', 'B'),
('C', 'D'),
('B', 'C'),
('B', 'E'),
('E', 'F');
--Option 1: Set-based loop
DECLARE @Level int = 1;
UPDATE s SET
[Level] = @Level
FROM #Sample s
WHERE NOT EXISTS( SELECT 1
FROM #Sample i
WHERE i.Child_ID = s.Parent_ID)
WHILE @@ROWCOUNT > 0
BEGIN
SET @Level += 1;
UPDATE s SET
[Level] = @Level
FROM #Sample s
WHERE EXISTS( SELECT 1
FROM #Sample i
WHERE i.Child_ID = s.Parent_ID
AND i.Level = @Level - 1)
END
SELECT *
FROM #Sample;
--Option 2: recursive CTE
WITH rCTE AS(
SELECT *, 1 AS calcLevel
FROM #Sample s
WHERE NOT EXISTS( SELECT 1
FROM #Sample i
WHERE i.Child_ID = s.Parent_ID)
UNION ALL
SELECT s.Parent_ID, s.Child_ID, s.Level, r.calcLevel + 1
FROM #Sample s
JOIN rCTE r ON s.Parent_ID = r.Child_ID
)
--SELECT * FROM rCTE
UPDATE s SET
Level = calcLevel
FROM #Sample s
JOIN rCTE r ON s.Parent_ID = r.Parent_ID
AND s.Child_ID = r.Child_ID;
SELECT *
FROM #Sample
GO
DROP TABLE #Sample
July 19, 2016 at 1:29 am
CTE Example
CREATE TABLE #Sample(
Parent_ID char(1),
Child_ID char(1),
[Level] int
);
INSERT INTO #Sample(Parent_ID, Child_ID)
VALUES
('A', 'B'),
('C', 'D'),
('B', 'C'),
('B', 'E'),
('E', 'F');
; with cte as
(
Select Parent_ID,Child_ID,0 as level1 from #Sample where Parent_ID not in (
Select distinct Child_ID from #Sample)
union all
Select a.Parent_ID,a.Child_ID,b.level1+1 from #Sample as a inner join cte as b on a.Parent_ID =b.Child_ID
)
Select * from cte
July 19, 2016 at 5:56 am
Hi,
you can try with below approach-
declare @t table(Parent_ID char(1),Child_ID char(1))
insert @t values ('A','B'),('C','D'),('B','C'),('B','E'),('E','F')
select t1.Parent_ID,t1.Child_ID,isnull(t2.count+t3.t3_cnt,1) Level from @t t1 left join
(select Parent_ID,count(Child_ID)count from @t group by Parent_ID) t2
on t1.Parent_ID=t2.Parent_ID
left join (select Child_ID,COUNT(*)t3_cnt from @t group by Child_ID) t3
on t3.Child_ID=t2.Parent_ID
order by 3
Thanks,
Anand
July 19, 2016 at 6:13 am
shirolkar.anand (7/19/2016)
Hi,you can try with below approach-
declare @t table(Parent_ID char(1),Child_ID char(1))
insert @t values ('A','B'),('C','D'),('B','C'),('B','E'),('E','F')
select t1.Parent_ID,t1.Child_ID,isnull(t2.count+t3.t3_cnt,1) Level from @t t1 left join
(select Parent_ID,count(Child_ID)count from @t group by Parent_ID) t2
on t1.Parent_ID=t2.Parent_ID
left join (select Child_ID,COUNT(*)t3_cnt from @t group by Child_ID) t3
on t3.Child_ID=t2.Parent_ID
order by 3
Thanks,
Anand
Your solution doesn't return the desired results. It will also return 3 different levels even if there are more.
July 19, 2016 at 7:05 am
Tweaked a bit to suit my requirement and it worked. Thank you Luis Cazares
July 19, 2016 at 7:08 am
JohnNash (7/19/2016)
Tweaked a bit to suit my requirement and it worked. Thank you Luis Cazares
Just be sure to understand it completely and ask any questions that you might have. 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply