Archive

Posts Tagged ‘UNION’

TSQL – Common Table Expressions (CTE) – Part 1 – Recursive Query

13.04.2011 2 comments

Let’s take a look at how CTE can be used to create a recursive query:

In a typical employee – supervisor scenario, A supervisor is an employee and an employee can be a supervisor of another employee(s).

This is also known as a self-referencing. For example:

image

The above table contains a list of Employee IDs and Employee Names. It also contains a column named ManagerID for Employee’s Manager. The Manager here is also an employee.

To generate a hierarchical list of all employees:
1.
we first need to retrieve root employee, i.e. top level employee,
2.
after the root level is retrieved, we need to find all child nodes for that root level,
3.
perform step 2 until all levels are retrieved.

This can be achieved using a Recursive Common Table Expression, which consists of two parts:
1. Retrieve root level – this is known as Anchor in a Recursive CTE
2.
retrieve all children of root level – this is known as Recursive Part in a Recursive CTE.

These process is performed recursively until all level are retrieved.

The Anchor part will be written as follows: (Retrieving root level)

SELECT Emp.ManagerID, Emp.EmpID, Emp.EmpName, 0 AS EmpLevel
FROM dbo.Employees Emp
WHERE ManagerID IS NULL

And, the recursive part is written as: (Retrieving all children of root), “EmployeeHierarchy” will be the CTE itself, hence making it recursive:

SELECT Emp.ManagerID, Emp.EmpID, Emp.EmpName, EmpLevel + 1
FROM dbo.[Employees] Emp
INNER JOIN EmployeeHierarchy AS EmpH ON Emp.ManagerID = EmpH.EmpID

After combining both parts the full query will be:

WITH EmployeeHierarchy(ManagerID, EmpID, EmpName, EmpLevel)
AS
( 
      SELECT Emp.ManagerID, Emp.EmpID, Emp.EmpName, 0 AS EmpLevel
      FROM dbo.Employees Emp
      WHERE ManagerID IS NULL 
 
      UNION ALL 
 
      SELECT Emp.ManagerID, Emp.EmpID, Emp.EmpName, EmpLevel + 1
      FROM dbo.Employees Emp
      INNER JOIN EmployeeHierarchy AS EmpH ON Emp.ManagerID = EmpH.EmpID
)     SELECT ManagerID, EmpID, EmpName, EmpLevel
      FROM EmployeeHierarchy
      ORDER BY ManagerID

The output of above query:

image

Download the sample scripts used in this example here.

Hope This Helps!

Vishal