Part 2 SQL query to get organization hierarchy

Link for all dot net and sql server video tutorial playlists

Link for slides, code samples and text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

To get the best out of this video, the following concepts need to be understood first. These are already discussed in SQL Server Tutorial.

Here is the problem definition:
1. Employees table contains the following columns
a) EmployeeId,
b) EmployeeName
c) ManagerId
2. If an EmployeeId is passed, the query should list down the entire organization hierarchy i.e who is the manager of the EmployeeId passed and who is managers manager and so on till full hierarchy is listed.

For example,
Scenario 1: If we pass David's EmployeeId to the query, then it should display the organization hierarchy starting from David.

Scenario 2: If we pass Lara's EmployeeId to the query, then it should display the organization hierarchy starting from Lara.

We will be Employees table for this demo. SQL to create and populate Employees table with test data
Create table Employees
(
EmployeeID int primary key identity,
EmployeeName nvarchar(50),
ManagerID int foreign key references Employees(EmployeeID)
)
GO

Insert into Employees values ('John', NULL)
Insert into Employees values ('Mark', NULL)
Insert into Employees values ('Steve', NULL)
Insert into Employees values ('Tom', NULL)
Insert into Employees values ('Lara', NULL)
Insert into Employees values ('Simon', NULL)
Insert into Employees values ('David', NULL)
Insert into Employees values ('Ben', NULL)
Insert into Employees values ('Stacy', NULL)
Insert into Employees values ('Sam', NULL)
GO

Update Employees Set ManagerID = 8 Where EmployeeName IN ('Mark', 'Steve', 'Lara')
Update Employees Set ManagerID = 2 Where EmployeeName IN ('Stacy', 'Simon')
Update Employees Set ManagerID = 3 Where EmployeeName IN ('Tom')
Update Employees Set ManagerID = 5 Where EmployeeName IN ('John', 'Sam')
Update Employees Set ManagerID = 4 Where EmployeeName IN ('David')
GO

Here is the SQL that does the job
Declare @ID int ;
Set @ID = 7;

WITH EmployeeCTE AS
(
Select EmployeeId, EmployeeName, ManagerID
From Employees
Where EmployeeId = @ID

UNION ALL

Select Employees.EmployeeId , Employees.EmployeeName, Employees.ManagerID
From Employees
JOIN EmployeeCTE
ON Employees.EmployeeId = EmployeeCTE.ManagerID
)

Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName
From EmployeeCTE E1
LEFT Join EmployeeCTE E2
ON E1.ManagerID = E2.EmployeeId

[Adinasa]

One of the most complex queries i have ever seen! great work!

[Ds_Unangi]

The way you break the problem into pieces and then one by one solve them....its really great...really impressed by your
keep it up. god bless you :D

[raghuuvamsii]

Excellent sir, I'm a big of fan of your way of teaching. Getting the knowledge effectively is good thing but sharing that is great thing.

[stuti.sharma.ranchi]

This is a great case! I went through self-join, CTE, and recursive CTE videos, and last but not least this interview case. Very comprehensive understanding of these concepts now. Thank you!

[vishwanathmali]

nice venkat sir it is really great job for needy people god bless u

[alihameed]

Hello Venkat, i am a big fan of you. You really are a great person indeed. I just pray may ALLAH the almighty shower HIS Countless blessings upon you. Aameen

[siddhukr]

I have 10, 000 records of hierarchy data and if i go by this method it would have a serious impact on performance . So Sql has introduced a new datatype named hierarcyid which would give a better performance . Can you please post a video with above as example .

[kamleshnakade]

Great Work Venkat. Really Impressed by the way of representation and examples, which are very clear to understand.
Thanks a ton!!

[davidramirez]

Thnks Venkat, Can't stop watchig your work and great tutorials.

[SanjeevVermasanjeevr]

your every video is very easy to under stand and really very help full.

[FlareSoul]

Loved this real world SQL application!! Please make more SQL videos like this!

[nanani]

Hi @kudvenkat,

If there is Union All, for every level up, David Record has to be appended once correct? There must be some duplicates atleast? But your output doesn't have any duplicates. Can you let me if I'm thinking correct, will there be any duplicates at all?

[neemkarolimaharajji-jt]

sir aapne set and declare k baad 0id likha h ya kuch or? kuch smjh nhi aara?

[prattipatinaveena]

Sir I have one doubt.if I am passing anyone id in the company I want only ceo name

[Arvindkumar-wugj]

Hi Venkat, below query is not working in
Declare @ID int ;
Set @ID = 7;
WITH EmployeeCTE AS
(
Select e.EmployeeId, e.EmployeeName, e.ManagerID
From Employees e
Where EmployeeId = @ID

UNION ALL

Select Employees.EmployeeId, Employees.EmployeeName, Employees.ManagerID
From Employees
JOIN EmployeeCTE
ON Employees.EmployeeId = EmployeeCTE.ManagerID
)

Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName
From EmployeeCTE E1
inner Join EmployeeCTE E2
ON E1.ManagerID = E2.EmployeeId

In MSSQL 2012

[bipin]

In case of row number... . For getting the nth highest salary.. We can use distinct instead of top 1... I think this will work..

[anbumani]

OK i got it. When the query before union runs already the CTE has one row. Then after union query, it starts processing from that available first row. Am i correct? Thanks.

[hirajijadhav]

Hi venkat sir recently I attended interview and there interviewer asked me SQL query as there are two tables item and item Rate

item table consist of
item id Item Name
1. Parker
2. Marie
3. Good Day
4. Monaco

item Rate consist of
ItemId ItemRate
1. 5
3. 20


and Query was.
select I.iemName from item I
left join ItemRate R
on I.itemId!=R.itemId
where R.itemRate>5


what is the output of above query please explain

[sumitjangir]

Hello Sir,
I have a doubt,
This result can be get by following query

select a.Employeename, isnull(b.employeename, 'No boss') as manager
from Employees a left join
Employees b on a.ManagerID = b.EmployeeId

then why we use CTE or recursive query?

[alwaysbehappy]

I got an error :- The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Please Explain it.