Click here to Skip to main content
Click here to Skip to main content

Common Table Expression to find all related nodes

, 12 Nov 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
CTE To find all the related nodes in a hierarcy

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Jobless Creature
Software Developer (Senior) TATA Communications
India India
I have a total experience of around 5 years out of which 4 years in MS technologies. I have a passion towards coding.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 12 Nov 2013
Article Copyright 2013 by Jobless Creature
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid