Part 2 SQL query to get organization hierarchy

Link for all dot net and sql server video tutorial playlists
   / kudvenkat  

Link for slides, code samples and text version of the video
http://csharp-video-tutorials.blogspo...

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.
   / @aarvikitchen5572  

To get the best out of this video, the following concepts need to be understood first. These are already discussed in SQL Server Tutorial.
1. Self-Join -    • Self join in sql server - Part 14  
2. CTE -    • CTE in sql server   Part 49  
3. Recursive CTE -    • Recursive CTE in sql server   Part 51  

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