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.