SQL query to get organization hierarchy

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
2. CTE
3. Recursive CTE



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.



Consider the following organization hierarchy chart


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


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


We will be using the following 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') asManagerName

From EmployeeCTE E1

LEFT Join EmployeeCTE E2

ON E1.ManagerID = E2.EmployeeId