CODEWITHSUNDEEP
sqlsql-server-2008t-sqlhierarchyid
Luke101
Luke101

Reputation: 65308

How to find ALL descendants using HierarchyID for SQL Server

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?

Upvotes: 14

Views: 18170

Answers (3)

Marc Gravell
Marc Gravell

Reputation: 1064204

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

Upvotes: 27

SolanumVexus
SolanumVexus

Reputation: 9

I'm a fan of CTEs for this sort of query because you have some flexibility in whether to return just the children, just the parent, or both depending on how you structure your code. In this case I'm returning a UNION of both, just for example.

declare @MyTable table
(
    ID int not null,
    HierId hierarchyid null
);

declare @id int
set @id = 1

;with parent (TenantId, HierId, IsParent) as
(
    select
        t.ID
        ,t.HierId
        ,cast(1 as bit) as IsParent
    from @MyTable t
    where t.ID = @id
), children as
(
    select
        t.ID
        ,t.HierId
        ,cast(0 as bit) as IsParent
    from 
        @MyTable t
        inner join parent p
            on t.HierId.IsDescendantOf(p.HierId) = 1
)
select
    *
from parent
UNION
select * 
from children

Upvotes: 0

Timothy Walters
Timothy Walters

Reputation: 16884

I'll assume for my example that your table is something like the following:

DECLARE TABLE MyTable
(
    HID hierarchyid PRIMARY KEY,
    ID int IDENTITY(1, 1),
    SomeText varchar(50)
);

If you want all decendants of a node with ID 3, down to a max level (from root) of 5:

DECLARE @searchNode hierarchyid;

SELECT @searchNode = HID
FROM MyTable
WHERE ID = 3;

SELECT *
FROM MyTable
WHERE HID.IsDescendantOf(@searchNode)
AND HID.GetLevel() <= 5;

If instead you want 2 levels of children under the requested node you would need to capture the level of your search node in the first select and change the comparison to something like

WHERE HID.IsDescendantOf(@searchNode) = 1 
AND HID.GetLevel() <= (@searchLevel + 2);

Upvotes: 16

Related Questions