Common Table Expression to find all related nodes

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

1. CTE is a interesting query in fetching the hierarchical data.
2. The MAX RECURSION option can used to control the output level.
3. Max Recursion (0) means the iteration level has no limit.
4. 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.