sql server 2005 - 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

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