HierarchyID: Get all descendants for a list of parents

It might be useful for someone. I found way of doing this by self-joining query:

SELECT p2.* FROM PersonHierarchy p1
LEFT JOIN PersonHierarchy p2 
    ON p2.PersonHierarchyID.IsDescendantOf(p1.PersonHierarchyID) = 1
WHERE 
    p1.PersonID IN (100, 110, 120, 130)

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!