2

I try to build a menu using the datatype hierarchyid.

I have the root node and the current selected node. now I want to list of all elements that are related wetween root and selected node AND there siblings.

I get all related elements with following sql query

DECLARE @rootNode hierarchyid, @selectedNode hierarchyid

SELECT @rootNode = MenuNode FROM CMS_Menu WHERE MenuItemID = 3;

SELECT @selectedNode = MenuNode FROM CMS_Menu WHERE MenuItemID =15;

SELECT CMS_Menu.MenuNode  
FROM CMS_Menu
WHERE @selectedNode.IsDescendantOf(MenuNode) = 1 /*all related elements*/
AND MenuNode.GetLevel() >= @rootNode.GetLevel() /*nothing below root*/

Now I have to do something like MenuNode.GetAncestor(1) = result for each row in the query above.

Does anyone have an idea how to get this in a sql query?

Thanks : )

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Thomas Brand
  • 23
  • 2

1 Answers1

0

Not entirely sure I understand the question but could you not do something like the following with the WHERE clause:

WHERE @selectedNode.IsDescendantOf(MenuNode.GetAncestor(1)) = 1

Tom

Tom Hunter
  • 5,714
  • 10
  • 51
  • 76