HierarchyID: Get all descendants for a list of parents

Question

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.

Answer 1

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)

Answer 2

You can use this query

Select 
   child.*, 
   child.[PersonHierarchyID].GetLevel(),
   child.[PersonHierarchyID].GetAncestor(1)
From 
   PersonHierarchy as parents
   Inner Join PersonHierarchy as child
               On child.[PersonHierarchyID].IsDescendantOf(parents.[PersonHierarchyID] ) = 1
Where
   parents.[PersonHierarchyID] = 0x68