CTE – A T-SQL Solution to Recursive Queries

We often need to deal with hierarchies (tree structures) in SQL. Some common examples are employee hierarchies, parent-child hierarchies etc.

 

There is a clean solution to achieve it using CTE (Common Table Expressions) which is demonstrated below with a simple example.

 

The diagram below represents a parent-child hierarchy:

 

  

And the table “Persons” implements the above tree:

 

 

 

Here is the CTE approach (recursion) to retrieve all the descendants of a given person.

 

–To find all the descendants of Sam

 

DECLARE @parentID int;

SET @parentID = 1; –Sam

 

WITH Descendants (PersonID,ParentID)

AS

(

      SELECT PersonID, ParentID FROM Persons WHERE ParentID = @parentID

      UNION ALL

      SELECT Persons.PersonID, Persons.ParentID FROM Persons

            INNER JOIN Descendants ON Persons.ParentID = Descendants.PersonID

)

 

SELECT Persons.PersonID, Persons.Name FROM Persons

INNER JOIN Descendants

      ON Persons.PersonID = Descendants.PersonID

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s