CODEWITHSUNDEEP
sqlsql-server-2008hierarchyid
san
san

Reputation: 11

How to find leaf nodes under a subtree using HierarchyId?

I am maintaining a tree structure using SQL Server 2008 HierarchyId. My table structure is like this:

CREATE TABLE [Employee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[HId] [hierarchyid] NOT NULL,
[Lvl]  AS ([HId].[GetLevel]()) PERSISTED,
[Name] [varchar](200) NOT NULL)

All the leaf data is not in the same level. I can find out all the leaf records of the table. But i need to find out the leaf data for any given subtree. In other words, i need to "find all subordinates for any given manager who does not manage any other employee".

Sample data:

Id Path Lvl Name 
1 / 0 Emp1 
2 /1/ 1 Emp2 
3 /1/1/ 2 Emp3 
4 /1/1/1/ 3 Emp4 
5 /1/2/ 2 Emp5 
6 /1/2/1/ 3 Emp6 
7 /1/2/1/1/ 4 Emp7 
8 /1/2/1/2/ 4 Emp8 
9 /1/2/2/ 3 Emp9 
10 /1/2/2/1/ 4 Emp10 
11 /1/2/2/2/ 4 Emp11 
12 /1/2/2/3/ 4 Emp12 
13 /1/2/2/4/ 4 Emp13 
14 /1/2/2/5/ 4 Emp14 
25 /1/2/3/ 3 Emp15 
26 /1/2/3/1/ 4 Emp16 
27 /1/2/3/2/ 4 Emp17 
28 /1/2/4/ 3 Emp18 
29 /1/2/4/1/ 4 Emp19 
30 /1/2/4/2/ 4 Emp20 
15 /1/3/ 2 Emp21 
16 /1/3/1/ 3 Emp22 
18 /1/3/1/1/ 4 Emp23 
19 /1/3/1/2/ 4 Emp24 
17 /1/3/2/ 3 Emp25 
20 /1/3/2/1/ 4 Emp26 
21 /1/3/2/2/ 4 Emp27 
22 /1/3/2/3/ 4 Emp28 
23 /1/3/2/4/ 4 Emp29 
24 /1/3/2/5/ 4 Emp3

Please help me find this.

Upvotes: 1

Views: 1141

Answers (1)

podiluska
podiluska

Reputation: 51494

You want the IsDescendantOf function.

eg:

declare @manager hierarchyid
select @Manager = HID from employee where --- criteria for given manager

Select *
from employee
where Hid.IsDescendantOf(@manager)=1

To find those which manage no others, add

and Hid.GetDescendant(null,null) is null

Upvotes: 1

Related Questions