Skip to content

Instantly share code, notes, and snippets.

@sinairv
Created July 25, 2012 02:20
Show Gist options
  • Save sinairv/3173996 to your computer and use it in GitHub Desktop.
Save sinairv/3173996 to your computer and use it in GitHub Desktop.
Understanding recursive CTE
-- taken from: http://www.databasejournal.com/features/mssql/article.php/10894_3502676_2/Common-Table-Expressions-CTE-on-SQL-2005.htm
-- recursive CTEs are divided in two parts 1. above "UNION ALL", and 2. below that
-- the statements on part 1 should be run without recursion to the CTE itself, this is our so-called recursion exit condition (anchor member)
-- the statements on part 2 use the CTE name in their select clause, this is the main recursion (recursive member)
-- the script below lists employees as well as their direct managers.
USE AdventureWorks ;
GO
WITH DirectReports(LoginID, ManagerID, EmployeeID) AS
(
SELECT LoginID, ManagerID, EmployeeID
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.LoginID, e.ManagerID, e.EmployeeID
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT *
FROM DirectReports ;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment