Use hierarchyid to query Hierarchical data

In this SQL minute we’ll look at how you can use hierarchyid to query hierarchy data.  This is a two-part article.  In this article we look at how to do one query to get portion of the hierarchy.  Next week, we will look to see how to query the entire tree.

If you’re studying for the 70-761 exam, you’ll find there aren’t many intermediate SQL problems with explanation for you to solve.  To help you out I’ve put together my SQL Practice Work book.  When you subscribe to receive it, you’ll not only get my workbook with video answers, but an engaging problem to solve sent to your inbox each week.

For this problem we will use the AdventureWorks database.

Who works for JoLynn?

Hidden in the Employee table is AdventureWorks’ organizational structure. Though the HRIS specialist is adept at basic SELECT and JOINS, she is having a hard time finding out who reports to JoLynn Dobney.

Can you help her by writing some T-SQL to find all of JoLynn’s direct reports? For each direct report, output their JobTitle, First Name, and Last Name.

Write your query, and then look below to see how I did it!

Answer using hierarchyid functions.

Before we get to the answer lets get some terminology under our belt.  Hierarchical data can be though of as a tree.  In the diagram below you’ll see an organization chart.  The tree is upside down, as it “branches” down the page, instead of up to the sky.

Notice that the tree starts with a Root, this is the tree’s beginning.

Also, each node in the tree is connected to zero or more other nodes.  Any node can be considered a parent.  Any nodes branching from it are children.

Using the family tree metaphor, the parent has decedents, which are the children.  And the children have ancestors, which are parents and grandparents…

I’m old school, so most of the hierarchical data I’m used to using in a database is in the form of a parentID column being the foreign key to a primary key.

For example, the EmployeeID is the primary key and the Manager ID the parent.

In the adventure works database this is done differently.  Here the reporting structure is conveyed using the hierarchyid type.  This type provides a means to relate the parent-child relationships using one field.

HierarchyID Type built-in Functions

A hierarchyid value is a hash value.  Its value is in the built-in function we can use to navigate the hierarchy, such as:

• GetAncestor – Return the hierarchyid of the parent of the current row.
• GetDescendant – Return the hierarchyid of a child of the current row.
• GetRoot – Return the top (root) of the hierarchy.

To solve this problem I’m going to use the GetAncestor function to return the parent hierarchyid.  The idea is to first get the hierarchyid for JoLynn, and then return all records whose ancestor hierarchyid matches hers.

Below is the code I used to write the query. In the first part we get JoLynn’s hierarchyid.

In the second part we get hierarchyid of an employee’s boss. If it matches JoLynn’s hierarchyid, then we keep the row in our result…

--get direct reports  
--Part 1:  Find JoLynn’s hierarchyID in Employee Database
DECLARE @CurrentEmployee HIERARCHYID;
SELECT @CurrentEmployee=OrganizationNode
FROM   HumanResources.Employee E
       INNER JOIN Person.Person P ON
       E.BusinessEntityID = P.BusinessEntityID
WHERE  P.FirstName ='JoLynn' AND P.LastName ='Dobney';

--Part 2:  Find all direct reports for JoLynn
SELECT E.JobTitle,
       P.FirstName,
       P.LastName
FROM   HumanResources.Employee E
       INNER JOIN Person.Person P ON
       E.BusinessEntityID = P.BusinessEntityID
WHERE  OrganizationNode.GetAncestor(1)= @CurrentEmployee;

Here is another way to write the same code using a subquery!

SELECT E.JobTitle,
       P.FirstName,
       P.LastName
FROM   HumanResources.Employee E
       INNER JOIN Person.Person P ON
       E.BusinessEntityID = P.BusinessEntityID
WHERE  OrganizationNode.GetAncestor(1)=  
       (SELECT OrganizationNode
        FROM   HumanResources.Employee E
               INNER JOIN Person.Person P ON
               E.BusinessEntityID = P.BusinessEntityID
        WHERE  P.FirstName ='JoLynn' AND P.LastName ='Dobney')

The subquery returns a single value, the same as returned in the first step where we saved it to @currentEmployee.

Let me know if you have another way to solve the problem. Just post it in the comments. Also, if you want to get on my list to get sent a new problem to solve each week, don’t forget to SQL Practice Work book.

The post Use hierarchyid to query Hierarchical data appeared first on Essential SQL.