I need to find all descendants of a category using HierarchyID
for SQL Server
.
I know how to find direct children but I would like to find children of children of children and so on.
Is there a way to do this using the HierarchyID
?
If you have the root of the tree you want, can't you just use:
DECLARE @root hierarchyID;
SELECT @root = col
FROM yourTable
WHERE [whatever uniquely identifies this row]
SELECT *
FROM yourTable
WHERE col.IsDescendantOf(@root) = 1