HierarchyID: Get all descendants for a list of parents

I have a list of parent ids like this 100, 110, 120, 130 which is dynamic and can change. I want to get all descendants for specified parents in a single set. To get children for a single parent I used such query:

WITH parent AS (
    SELECT PersonHierarchyID FROM PersonHierarchy
    WHERE PersonID = 100    
)
SELECT * FROM PersonHierarchy
WHERE PersonHierarchyID.IsDescendantOf((SELECT * FROM parent)) = 1

Have no idea how to do that for multiple parents. My first try was to write something like several unions, however I'm sure that there should be smarter way of doing this.

SELECT * FROM PersonHierarchy 
WHERE PersonHierarchyID.IsDescendantOf(
    (SELECT PersonHierarchyID FROM PersonHierarchy WHERE PersonID = 100)
) = 1
UNION ALL
SELECT * FROM PersonHierarchy 
WHERE PersonHierarchyID.IsDescendantOf(
    (SELECT PersonHierarchyID FROM PersonHierarchy WHERE PersonID = 110)
) = 1
UNION ALL ...

P.S. Also I found such query to select list of ids which might be helpful:

SELECT * FROM (VALUES (100), (110), (120), (130)) AS Parent(ParentID)

To summarize, my goal is to write query which accepts array of parent IDs as a parameter and returns all their descendants in a single set.

Solution

You're thinking too hard.

WITH parent AS (
    SELECT PersonHierarchyID FROM PersonHierarchy
    WHERE PersonID in (<list of parents>)    
)
SELECT * FROM PersonHierarchy
WHERE PersonHierarchyID.IsDescendantOf((SELECT * FROM parent)) = 1

I'd write it like this, though:

select child.*
from PersonHierarchy as parent
inner join PersonHierarchy as child
   on child.PersonHierarchyID.IsDescendantOf(
       parent.PersonHierarchyId
   ) = 1
where Parent.PersonId in (<list of parents>)

Note: in both cases, this could be slow as it has to evaluate IsDescendantOf for n*m entries (with n being the cardinality of the list of parents and m being the cardinality of the table).

I recently had a similar problem and I solved it by writing a table-valued function that, given a hierarchyId would return all of the parents. Let's look at a solution to your problem using that approach. First, the function:

CREATE FUNCTION [dbo].[GetAllAncestors] (@h HierarchyId, @IncludeSelf bit)
RETURNS TABLE
AS RETURN

    WITH cte AS (
        SELECT @h AS h, 1 AS IncludeSelf
    )
    SELECT @h.GetAncestor(n.NumberId) AS Hierarchy
    FROM ref.Number AS n
    WHERE n.NumberId <= @h.GetLevel()
    AND n.NumberId >= 1

    UNION ALL

    SELECT h
    FROM cte
    WHERE IncludeSelf = @IncludeSelf

It assumes that you have a Numbers table. They're immensely useful. If you don't have one, look at the accepted answer here. Let's talk about that function for a second. In essence, it says "For the passed in hierarchyId, get the current level. Then get call GetAncestor until you're at the top of the hierarchy.". Note that it optionally returns the passed in hierarchyId. In my case, I wanted to consider a record an ancestor of itself. You may or may not want to.

Moving onto a solution that uses this, we get something like:

select child.*
from PersonHierarchy as child
cross apply [dbo].[GetAllAncestors](child.PersonHierarchyId, 0) as ancestors
inner join PersonHierarchy as parent
  on parent.PersonHierarchyId = ancestors.Hierarchy
where parent.PersonId in (<list of parents>)

It may or may not work for you. Try it out and see!