Employee Manager Hierarchy (CTE - Common Table Expressions)

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.