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.

WITH Employee_CTE(employeeid,name,managerid) AS
(
   SELECT employeeid,name,managerid from employee where employeeid=5
   UNION ALL
   SELECT e.employeeid,e.name,e.managerid
   from employee e
   INNER JOIN Employee_CTE c ON e.employeeid = c.managerid
)
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.