How to find ALL descendants using HierarchyID for SQL Server

Luke101 picture Luke101 · Apr 17, 2010 · Viewed 16.7k times · Source

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?

Answer

Marc Gravell picture Marc Gravell · Apr 17, 2010

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