CODEWITHSUNDEEP
sql-serverhierarchy
vishal
vishal

Reputation: 207

Get Parent node of a given node using SQL hierarchyId

I have a table as CorporateStructure as following with following data:

╔════╦════════╦═════════════╗
║ Id ║  Name  ║ HierarchyId ║
╠════╬════════╬═════════════╣
║  1 ║ john   ║ /           ║
║  2 ║ smith  ║ /1          ║
║  3 ║ John2  ║ /1/1/       ║
║  4 ║ Collin ║ /1/1/1      ║
╚════╩════════╩═════════════╝

Id i have id 4 and i want to find the parent how can i do that?

Regards Vishal

Upvotes: 1

Views: 4378

Answers (1)

praveen
praveen

Reputation: 12271

As Michael has pointed out the literal value for smith (/1) and Collin (/1/1/1) doesn't seem to be a valid HierarchyId

Correcting those values you can get the parent node using GetAncestor

Try this :-

Select * from YourTable
where [HierarchyId] in (
           Select [HierarchyId].GetAncestor([HierarchyId].GetLevel()-1).ToString()
           from YourTable
           where id=4
           )

SQL FIDDLE Demo

Upvotes: 3

Related Questions