How to self JOIN recursively in SQL?

Question

I have a table:

Series
========
ID
SeriesName
ParentSeriesID

A series can be a "root" series, (ParentSeriesID is 0 or null) or it can have a Parent. A series can also be several levels down, i.e. its Parent has a Parent, which has a Parent, etc.

How can I query the table to get a Series by it's ID and ALL descendant Series' ?

So far I have tried:

 SELECT child.*
 FROM Series parent JOIN Series child ON child.ParentSeriesID = parent.ID
 WHERE parent.ID = @ParentID

But this only returns the first level of children, I want the parent node, and all "downstream" nodes. I am not sure how to progress from here.

Answer 1

Make use of CTE feature avaiable in slq server 2005 onwards for recurisve query

USE AdventureWorks
GO
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO

You can view example over here :

SQL SERVER – Simple Example of Recursive CTE

Answer 2

I just enhance the work of Thomas. If you need to get the depth of the hierarchy and getting the parentid here is the code.

This was almost the same with Thomas' work.

With Family As 
( 
    Select s.ID, s.ParentSeriesId, 0 as Depth
    From Series s
    Where ID = @ParentID <--- this was removed if you intend to get all hierarchy of the record. You can retain this if you want
  Union All 
     Select s2.ID, s2.ParentSeriesId < --- change to **Family.ParentID**, Depth + 1
     From Series s2
     Join Family 
         On Family.ID = s2.ParentSeriesId 
) 
 Select *
 From Family 

That's all. I know it's too late but I hope anyone who encounter this may help them. Thanks Thomas for the original code. :)

Answer 3

If you are using SQL Server 2005+, you can use common-table expressions

With Family As 
( 
Select s.ID, s.ParentSeriesId, 0 as Depth
From Series s
Where ID = @ParentID 
Union All 
Select s2.ID, s2.ParentSeriesId, Depth + 1
From Series s2
    Join Family 
        On Family.ID = s2.ParentSeriesId 
) 
Select *
From Family 

For more:

Recursive Queries Using Common Table Expressions