Ancestor and Descendant IDs/info list using Common Table Expressions
Needed some TSQL code again to retrieve descendant and ancestor entries
in a self-referencing table. I've done this a couple of times already
and although I can write it from the top of my head, sometimes you just want to
make life easier and have a script you can just modify a bit to fit the
new requirement.
So what I usually use for recursions in SQL Server 2005 are Common
Table Expressions (the "WITH" keyword). This is only available in SQL
2005 and very easy/efficient for recursive.
This is definitely in BOL and articles all over nevertheless here's my basic script to get "Descendants and Self"
and "Ancestors and Self" information. In this case I only involved the
ID, ParentID, and Sequence but you may add more columns (ID and
ParentID are required to work). Also you might want to just retrieve
the ID and just perform a JOIN afterwards.
Please read full article from .NET Developer Notes on Ancestor and Descendant IDs/info using Common Table Expressions