65.9K
CodeProject is changing. Read more.
Home

Using GetAncestor() in a join

starIconstarIconstarIconstarIconstarIcon

5.00/5 (1 vote)

May 26, 2010

CPOL
viewsIcon

17780

GetAncestor() requires a slightly unusual outlook to get the required results

Scenario

I have a table using a heirarchyID to define a hierarchy of products. I now want to get all lowest level(5) nodes where the 3rd level(2) up has a specific attribute (ElementID = 8). I want to do this using a join!

Table Definition

CREATE TABLE [ProductNodes](
    [NodeID] [int] IDENTITY(1,1) NOT NULL,
    [NodeKey] [hierarchyid] NULL,
    [ElementID] [int] NULL,
    [ProductID] [int] NULL
) ON [PRIMARY]
First I get the nodes where my attribute matches the required value (ElementID = 8).
SELECT *
FROM ProductNodes PN
WHERE PN.ElementID = 8
Now to add the join that will get me the level 5 records. I want all the records which have PN nodekey as the parent 3 levels up.
SELECT PN2.ProductID
FROM ProductNodes PN
  INNER JOIN ProductNodes PN2 ON PN2.NodeKey.GetAncestor(3) = PN.NodeKey
WHERE PN.ElementID = 8
The actual data I want is from the secondary table (PN2) rather than selected table.