HierarchyID: Get all descendants for a list of parents — Not Working

Question

I'm working off this thread: HierarchyID: HierarchyID: Get all descendants for a list of parents

I have a table that uses a HierarchyID, and I need a query that gives me all descendants for specified parent(s) in a single set.

Here's my table, populated:

DECLARE @Ph TABLE (ProductHierarchyNode HIERARCHYID, ProductHierarchyId INT)
INSERT INTO @Ph (ProductHierarchyNode, ProductHierarchyId) VALUES 
(hierarchyid::Parse('/1/'), 1),
(hierarchyid::Parse('/1/1/'), 2),
(hierarchyid::Parse('/1/1/2/'), 3),
(hierarchyid::Parse('/1/1/2/1/'), 4)

This query works perfectly for a SINGLE id: 4. It gives me back that item, plus all of its descendants.

SELECT
    *
FROM 
    @Ph
WHERE 
    (SELECT ProductHierarchyNode FROM @Ph WHERE ProductHierarchyId = 4).IsDescendantOf(ProductHierarchyNode) = 1 

However, that query isn't very useful for a list of ID's. So for a list, I'm trying the answer in the thread I linked to above:

SELECT 
    child.*
FROM 
    @Ph as parent
    INNER JOIN @Ph as child on child.ProductHierarchyNode.IsDescendantOf(parent.ProductHierarchyNode) = 1
WHERE 
    parent.ProductHierarchyId in (4)

I'm sure I'm overlooking something obvious. Just not sure what it is. But this only returns me the parent item and no children.

Can't spot my error.

Answer 1

I feel like you have this backwards... you want DESCENDENTS, but you're asking for things where the parent is 4... nothing has a parent of 4.

Don't you really want this?

SELECT 
    child.*
FROM 
    @Ph as parent
    INNER JOIN @Ph as child on child.ProductHierarchyNode.IsDescendantOf(parent.ProductHierarchyNode) = 1
WHERE 
    parent.ProductHierarchyId in (1)

This returns all the rows you say you're expecting, and makes more sense to me as you're asking for all descendent of 1 (the root parent). 2 is a child/descendent of 1, 3 is a child/descendent of 2, and 4 is a child/descendent of 3.

The "WHERE parent.ProductHierarchyId IN (1)" is saying "find me all nodes where 1 is a parent/ancestor".

In the first query, you're asking for all nodes where 4 is a descendent, so that makes sense.

In the second query, you're asking for all descendents of 1. If you want "all ancestors of 4" that'd be a different query.