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.

Harpoon

Can you use Dynamic SQL? – Cuthburt

This query will be executed from my web application by ORM which supports stor procs. I think it's possible to have dynamic sql there. – Harpoon

Answer 1

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!

Guadalcanal

my hierarchy is too small to cause any performance issues it shouldn't be greater than 200 records, so for now first approach will work fine. Thank you for your help. – Harpoon

Think for the 2nd query you posted (not the cte) the join to child should be child.PersonHierarchyId.IsDescendantOf(parent.PersonHierarchyId) = 1 – Waterborne

I know this is old. But... if you are still around, would you mind taking a look at this: #57013603 – Palestine

I can say that in 2024 only inner join example works fast (second one). The rest is really slow. – Colon

Answer 2

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)

Harpoon

I know this is old. But... if you are still around, would you mind taking a look at this: #57013603 – Palestine

Answer 3

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

Splenic

I know this is old. But... if you are still around, would you mind taking a look at this: #57013603 – Palestine

Answer 4

Please check, this should work for you. I havent tried to modify your script but just put the query in loop. Hope it helps.

DECLARE @String VARCHAR(MAX) = '100, 110, 120, 130'
DECLARE @SQL VARCHAR(MAX)

SET @String = REPLACE(@String, CHAR(32), '') + ','

WHILE CHARINDEX(',', @String) > 0
    BEGIN
       DECLARE @ToString INT
       DECLARE @StringLength INT
       DECLARE @WorkingString VARCHAR(MAX)
       DECLARE @WorkingLength INT

       SET @ToString = CHARINDEX(',', @String)
       SET @StringLength = LEN(@String)
       SET @WorkingString = SUBSTRING(@String, 1, @ToString - 1)

       SET @String = SUBSTRING(@String, @ToString + 1, @StringLength)

       SET @WorkingString =  'SELECT * FROM PersonHierarchy ' + CHAR(13) + CHAR(10) 
                       + 'WHERE PersonHierarchyID.IsDescendantOf((SELECT PersonHierarchyID FROM PersonHierarchy WHERE PersonID = ' 
                       + @WorkingString + ')) = 1' + CHAR(13) + CHAR(10) 
                       + CASE WHEN CHARINDEX(',', @String) > 0 THEN 'UNION ALL'+ CHAR(13) + CHAR(10) ELSE '' END
       SET @SQL = ISNULL(@SQL,'') + @WorkingString
    END
PRINT @SQL
EXEC (@SQL)

Cuthburt