0

Problem: I need to extrapolate the parentID from the hierarchyID.

Example: My columnID is 8 and my current hierarchyID is /1/2/4/8. When I call columnName.GetAncestor(1), this will give me /1/2/4/. What I need is the ID of 4.

How do I do this? Or are hierarchyIDs only meant to be joined on?

I'm looking into the SqlHierarchyID.Parse() but I'm not understanding how to use it.

rlb.usa
  • 14,942
  • 16
  • 80
  • 128

1 Answers1

2

In order to get another field value for the GetAncestor(1) hierarchyid you need to do an inner join like this:

SELECT A.hid, A.myid, B.hid, B.myid 
FROM dbo.mytable A
INNER JOIN dbo.mytable B ON A.hid.GetAncestor(1) = B.hid

This returns each row with its parent in the same row (will probably miss the root row, though, but if you add a WHERE clause to get the hid you're interested in, then this should suffice).

Rainier Wolfcastle
  • 606
  • 2
  • 7
  • 18