Introduction
This is a SQL query on CTE. This query fetches the parent nodes, child nodes, siblings and all the related nodes in a hierarchy for a given node.
Recently I was working in a application which supports n-level hierarchy and there was a requirement where in for a given node, the query has to return all the parents, children and siblings. It also includes the nodes and child nodes of all the parents.
Scenario
The example deals with a organization with 3 departments, development, testing and human resource.
For a given department, the hierarchy goes like this.. CEO, Senior Manager, Manager, Project Leads, Senior Associates. For easy reading purpose, the employee names are designated with prefix "D" for development, "T" for testing and "H" for HR.
For a given Employee ID, the query will fetch the fellow team members, immediate seniors and their siblings and so on till the department head. In other words, the query will fetch all the employees of the department for a given employee Id.
The sample script is attached with the article (CTE.Zip)
DECLARE @EmpId INT;
SELECT @EmpId = 8;
WITH Parent AS
(
SELECT HE.EmpId, HE.EmpName, HE.EmpAge, HE.ManagerID
FROM HierarchyEmployees HE
WHERE HE.EmpId = @EmpId
UNION ALL
SELECT HE.EmpId, HE.EmpName, HE.EmpAge, HE.ManagerID
FROM HierarchyEmployees HE INNER JOIN Parent
On HE.EmpId = Parent.ManagerID
WHERE
HE.EmpId != Parent.EmpId
),
Children
AS
(
SELECT * FROM Parent
UNION ALL
SELECT HE.EmpId, HE.EmpName, HE.EmpAge, HE.ManagerID
FROM HierarchyEmployees HE
INNER JOIN Children ON HE.ManagerID = Children.EmpId
WHERE
HE.EmpId != Children.EmpId
)
SELECT * FROM Parent
UNION
SELECT * FROM Children
Points of Interest
- CTE is a interesting query in fetching the hierarchical data.
- The
MAX RECURSION
option can used to control the output level. Max Recursion (0)
means the iteration level has no limit. - In example, the CEO is not assigned as managers to senior manager. Doing so, will return all the records in the table for any given employee Id.
History
- 12 Nov 2013 - Initial version.