Click here to Skip to main content
14,173,165 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

1.5K views
1 bookmarked
Posted 26 Apr 2019
Licenced MIT

SQL Server Hierarchical Query using the hierarchyid Type

, 26 Apr 2019
Rate this:
Please Sign up or sign in to vote.
SQL Server Hierarchical Query using the hierarchyid type

In this SQL minute, we’ll look at how to create a SQL server hierarchical query using the hierarchyid data type. This is a two-part article. In Part one, you were introduced to the hierarchyid type. In this article, we dig deeper into the hierarchyid functions.

If you’re studying for the 70-761 exam, you’ll find there aren’t many intermediate SQL problems with explanations for you to solve. To help you out, I’ve put together my SQL Practice Workbook. 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.

Solving the Request using a SQL Server Hierarchical Query

AdventureWorks is concerned their manager’s span-of-control, that is how many employees report to them, is out of whack. To address this, the HR director would like a report that displays the job title, employee full name, their boss’ full name, and how many people, including themselves are in their organization.

Here is an example of what she is looking to receive from you:

SQL Server Hierarchical Query Results

SQL Server Hierarchical Query Results

We’ll tackle this query in steps.

Step 1 – Start Small

From our last problem, you should be familiar with the hierarchyid SQL type and its use to represent a tree like structure and it provides several functions, such as GetAncestor, to navigate hierarchies, such as org structures.

To start with this query, lets get the employee and their name. To do this, we’ll use an INNER JOIN:

SELECT E.JobTitle, 
        P.FirstName + ' ' + P.LastName as FullName
 FROM   HumanResources.Employee E
        INNER JOIN Person.Person P 
        ON P.BusinessEntityID = E.BusinessEntityID

Step 2 – Build Up Success

Now let’s build on that success and go grab the “report to” column values. This is a bit more complicated, as we need to introduce a self-join on the employee table to get the employee’s boss. In SQL server hierarchical query speak, this is the ancestor.

As you can see from the diagram, the top of the tree is the root, for the Server Manager, bosses are ancestors, and bosses’ direct reports descendants. Another way to look at the tree is to think of it as a family tree, where there are parents and children.

So, back to getting the Report To name… to do this, we’ll be joining to employee, and then again to person to get the name.

Here is a table diagram showing the relationships:

SQL Query Table Relationships

The tables are aliased: P and E are the person and employee table from our original query. R is the alias for the employee table to get the boss, and RP is an alias of the person table so we can match the boss to their name.

SELECT E.JobTitle, 
        P.FirstName + ' ' + P.LastName as FullName,
        RP.FirstName + ' ' + RP.LastName as ReportsTo
 FROM   HumanResources.Employee E
        INNER JOIN Person.Person P
        ON P.BusinessEntityID = E.BusinessEntityID
        INNER JOIN HumanResources.Employee R
        ON R.OrganizationNode = E.OrganizationNode.GetAncestor(1) 
        INNER JOIN Person.Person RP
        ON RP.BusinessEntityID = R.BusinessEntityID
 ORDER BY E.OrganizationNode

We’re getting closer! We do not have a way to get an employee’s boss, but check out the results, there is a small problem. The CEO is missing!

The problem with this query is that the CEO doesn’t report to anyone, so they aren’t included… we need to fix this.

Step 3 – Include CEO in SQL Server Hierarchical Query

The issue with the query is the GetAncestor function doesn’t return a value for the top-most person in the tree. Given this, our join condition can’t match. To fix this, we can add another join condition which specifically addresses the CEO.

Keep in mind when working with joins that your join conditions can include Boolean logic. We’re going to rewrite our join so it matches a record from aliased table R, if either of the following conditions are met:

  • The employee’s ancestor OrganizationNode matches another OrganizationNode. This is the existing join condition.
  • The employee’s OrganizationNode is the Root and OrganizationNode matches another. This is the CEO, as the root node is the topmost node, just like the CEO is the top person in an organization.

Here is the modified query, note the OR operator within the second join condition. Also, note how I enclose this second join condition, the one to handle the CEO, in parenthesis.

SELECT E.JobTitle, 
        P.FirstName + ' ' + P.LastName as FullName,
        RP.FirstName + ' ' + RP.LastName as ReportsTo
 FROM   HumanResources.Employee E
        INNER JOIN Person.Person P 
            ON P.BusinessEntityID = E.BusinessEntityID
        INNER JOIN HumanResources.Employee R 
            ON R.OrganizationNode = E.OrganizationNode.GetAncestor(1) OR
          (E.OrganizationNode = hierarchyid::GetRoot() AND
           R.OrganizationNode = E.OrganizationNode)
        INNER JOIN Person.Person RP
            ON RP.BusinessEntityID = R.BusinessEntityID
 ORDER BY E.OrganizationNode

Address Org Size using Hierarchyid Functions

The last step to complete is adding the org size to our results. At first, you may think you’ll need to write some sort of recursive CTE to traverse the tree and count up reports, but once you think about it, you’ll realize you can simply test whether an employee’s OrganizationNode is a descendant of the current employees.

This becomes a simple job, as you can use the IsDecendantOf function and a correlated subquery to count rows where this condition is met. The subquery is correlated, as part of its criteria includes a reference to the outer query.

Here is an example of the completed SQL server hierarchical query.

SELECT E.JobTitle, 
        P.FirstName + ' ' + P.LastName as FullName,
        RP.FirstName + ' ' + RP.LastName as ReportsTo,
        (SELECT COUNT(1)
     FROM   HumanResources.Employee
     WHERE  OrganizationNode.IsDescendantOf(E.OrganizationNode) = 1) as OrgSize
 FROM   HumanResources.Employee E
        INNER JOIN Person.Person P
        ON P.BusinessEntityID = E.BusinessEntityID
        INNER JOIN HumanResources.Employee R
        ON R.OrganizationNode = E.OrganizationNode.GetAncestor(1) OR
          (E.OrganizationNode = hierarchyid::GetRoot() AND
       R.OrganizationNode = E.OrganizationNode)
        INNER JOIN Person.Person RP
        ON RP.BusinessEntityID = R.BusinessEntityID
 ORDER BY E.OrganizationNode

Now that you have seen how to query tables that use the hierarchyid type, what other kinds of problems can you see solving with this type’s built-in functions?

Also, please keep in mind, depending on your database size, you may be better off using a different type of design to represent your hierarchy.

If you have any comments regarding this post, or if you have any ideas on representing a tree, let us know in the comments below.

The post SQL Server Hierarchical Query using the hierarchyid type appeared first on Essential SQL.

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here ==> http://www.essentialsql.com/get-started-with-sql-server/

You may also be interested in...

Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web06 | 2.8.190524.3 | Last Updated 26 Apr 2019
Article Copyright 2019 by essentialSQL
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid