Sql-server – HierarchyID: Get all descendants for a list of parents

hierarchysql servert-sql

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.

Best Answer

One way to accomplish this is:

USE tempdb;
CREATE TABLE dbo.PersonHierarchy
(
    PersonHierarchyID HierarchyID
);

INSERT INTO dbo.PersonHierarchy VALUES ('/1/');
INSERT INTO dbo.PersonHierarchy VALUES ('/1/1/');
INSERT INTO dbo.PersonHierarchy VALUES ('/1/2/');
INSERT INTO dbo.PersonHierarchy VALUES ('/2/');
INSERT INTO dbo.PersonHierarchy VALUES ('/2/1/');

SELECT *
FROM dbo.PersonHierarchy;

DECLARE @T TABLE 
(
    PersonHierarchyID HierarchyID
);

INSERT INTO @T
VALUES ('/1/'), ('/2/');

WITH parent AS (
    SELECT p.PersonHierarchyID 
    FROM dbo.PersonHierarchy p
        INNER JOIN @T t ON p.PersonHierarchyID = t.PersonHierarchyID
)
SELECT ph.PersonHierarchyID.ToString(), parent.PersonHierarchyID.ToString() /* select only the necessary columns, do NOT use SELECT *  */
FROM dbo.PersonHierarchy ph
    INNER JOIN parent ON ph.PersonHierarchyID.IsDescendantOf(parent.PersonHierarchyID) = 1
ORDER BY ph.PersonHierarchyID; /* always include an ORDER BY */
Related Question