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

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