Employee Manager Hierarchy (CTE - Common Table Expressions)

This blog illustrates how to get the hierarchy from the same table.

In this blog, we will see how to get the Employee-Manager hierarchy from the same table. 
 
Columns
 
EmployeeId, EmployeeName, ManagerId
 
The query was to get the list of manager hierarchy for a particular employee.
 
Let's say we have the below table.
 
 EmployeeID     Name                 ManagerId
 1  JB  NULL
 2  UB  NULL
 3  RK  1
 4  KK  2
 5    MG     3
 
Now, we need to get the list of Names of manager hierarchy for employee - "MG".
 
I hope you remember the "WITH" Common_Table_Expression. If not, then you can refer to here. It is a T-SQL expression.
 
According to Microsoft Docs - "It specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression."
 
Based on the above definition, we can get the expected result by the below T-SQL query.
  1. WITH Employee_CTE(employeeid,name,managerid) AS  
  2. (  
  3.    SELECT employeeid,name,managerid from employee where employeeid=5  
  4.    UNION ALL  
  5.    SELECT e.employeeid,e.name,e.managerid  
  6.    from employee e   
  7.    INNER JOIN Employee_CTE c ON e.employeeid = c.managerid  
  8. )  
  9. SELECT * FROM Employee_CTE order by employeeid  
Result
 
  EmployeeID  Name  ManagerId
 1     JB  NULL
 3  RK  1
 5  MG  3
 
So, as you can see above, we got the records where JB is the manager of RK and RK is the manager of MG. 
 
I hope, now, you're clear with CTE and its usage.