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.