TSQL hierarchical tree access through CTE
In many web/desktop based applications, Navigation Menus and other entities like Employee hierarchy are stored as a hierarchy in a single table. Having a recursive foreign key pointing to a primary key value of the same table, each menu item is linked to the parent menu item. This is to establish a tree like structure as shown below.
Below can be the probable table structure to store such menu hierarchy.
|Column Name||Data Type||Constraints|
|EmployeeID||Int (Identity)||Primary Key|
As mentioned above, it is using a recursive key i.e. Primary key MenuID is referred as a Foreign Key in the same table to indicate the Parent Menu ID.
At any point, we may find it easy to add an Employee at a specific level. However, if we want to check the hierarchy of any specific Employee, we need to iterate upward or downward through the ReportigTo IDs to get the details. Alternatively, we can use Common Table Expression (CTE) to do this.
Below is the query we can use:
Declare @EmpName varchar(50) = ‘Emp1’
;With CteTree As
Select 1 as Level, E.*
From Employees E
Where E.FirstName like @EmpName
Select Level + 1, E.*
From Employees E
Inner Join CteTree cte
On E.EmployeeID = cte.ReportTo
Select * from CteTree
Order by Level
The above CTE has two queries adjoined using “Union All” clause. The first query retrieves all the records from Employee table having employee’s first name, similar to value of @EmpName variable. The second query again retrieves all he records but with an additional recursive join to the CTE itself to map EmployeeID with the ReportTo of CTE.
This is to recursively retrieve the records hierarchically until the ReportingTo is not Null, in other words, is not set.
This query can be tweaked to use for any hierarchical data where there is a recursive key defined on save table.