This is one of my best short article in this blog. A very interesting one and I like the most.
Usually, we will face this scenario like -> Fetch an employee and get the entire hierarchy of an employee like. A is reporting to B, B is reporting to C, C is reporting to D.
If I need to find who is reporting to C, we need to fetch B followed by A and its depth. A fantastic Optimized solution to retrieve this information is,
drop table Venkat_SampleTable
create table Venkat_SampleTable(id int, nam varchar(10),bossid int)
insert into Venkat_SampleTable values(1,'venkat',0)
insert into Venkat_SampleTable values(2,'Arun',1)
insert into Venkat_SampleTable values(3,'Suba',1)
insert into Venkat_SampleTable values(4,'Karthi',2)
insert into Venkat_SampleTable values(5,'Krishiv',3)
insert into Venkat_SampleTable values(6,'Santhi',3)
select * from Venkat_SampleTable
DECLARE @boss_id int
SET @boss_id = 2;
WITH Venkat_CTE_Table (id, nam, BossID, Depth)
AS
(
SELECT id, nam, BossID, 0 AS Depth
FROM Venkat_SampleTable WHERE id = @boss_id
UNION ALL
SELECT Venkat_SampleTable.id, Venkat_SampleTable.nam, Venkat_SampleTable.BossID, Venkat_CTE_Table.Depth + 1 AS Depth FROM Venkat_SampleTable
JOIN Venkat_CTE_Table ON Venkat_SampleTable.BossID = Venkat_CTE_Table.id
)
SELECT * FROM Venkat_CTE_Table
Cheers,
Venkatesan Prabu .J
http://venkattechnicalblog.blogspot.com/