Microsoft® SQL Server® 2012 Bible

329

Chapter 13: Working with Hierarchies

13

DECLARE @EmpNode HierarchyID

SELECT @EmpNode = OrganizationNode

FROM HumanResources.Employee

WHERE OrganizationNode = '/3/3/'

SELECT e.BusinessEntityID AS BusEntID,

p.FirstName + ' ' + p.LastName AS 'Name',

e.JobTitle,

@EmpNode.IsDescendantOf(OrganizationNode)

FROM HumanResources.Employee e

INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID

WHERE @EmpNode.IsDescendantOf(OrganizationNode) = 1

The results, shown next, show the three descendant nodes, and in the fourth column you

can tell by the value of 1 that they are a descendant of A. Scott Wright.

BusEntID Name JobTitle No column name)

-------- -------------- ------------------ ----------------

1 Ken Sánchez Chief Executive Officer 1

25 James Hamilton Vice President of Production 1

222 A. Scott Wright Master Scheduler 1

Performing a Subtree Search

If you fl ip the IsDescendantOf() method around, you can search a subtree to locate

all descendants of the specifi ed node. You can start to see the fl exibility in these exam-

ples in that the IsDescendantOf() method can be used with a column as well as a

HierarchyID variable. This example varies from the previous in that the HierarchyID

variable is used as a parameter of the IsDescendantOf() method.

In this example, you grab the HierarchyID of the Information Services Manager and use

that to return the descendants of that HierarchyID.

DECLARE @EmpNode HierarchyID

SELECT @EmpNode = OrganizationNode

FROM HumanResources.Employee

WHERE OrganizationNode = '/5/'

SELECT e.BusinessEntityID AS BusEntID,

p.FirstName + ' ' + p.LastName AS 'Name',

e.OrganizationNode.ToString() AS 'Hierarchy',

e.OrganizationLevel

FROM HumanResources.Employee e

INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID

WHERE OrganizationNode.IsDescendantOf(@EmpNode) = 1

c13.indd 329c13.indd 329 7/30/2012 4:47:04 PM7/30/2012 4:47:04 PM

http://www.it-ebooks.info