i've a Problem with the hierarchyID and UserRights for a Menu. I only want to give the User the Right for Level 4 for example and my QUery should automaticly select all Parent from the Level 4 Child. How to do this ?
Do you understand my Problem ? I simply want all Parents (ancestors) from a child.
Greets Manuel
I've been working a lot with HierarchyId lately and I came across this question looking for answers to a different question. I thought I'd throw this example in the mix as it accounts for a few things. First, you can get your conditional expression in there without a recursive CTE. Second, GetDescendantOf is inclusive so you don't need to check t1.NodeId = t2.NodeId
(and I generally prefer joins to subqueries). Here's a full demo you can play with:
BEGIN TRANSACTION
CREATE TABLE #HierarchyDemo
(
NodeId HIERARCHYID PRIMARY KEY NOT NULL,
Description AS NodeId.ToString(),
Depth AS NodeId.GetLevel()
)
INSERT INTO #HierarchyDemo VALUES ( HierarchyId::GetRoot() );
INSERT INTO #HierarchyDemo VALUES ( CAST ('/1979/' AS HIERARCHYID) );
INSERT INTO #HierarchyDemo VALUES ( CAST ('/2012/' AS HIERARCHYID) );
INSERT INTO #HierarchyDemo VALUES ( CAST ('/2012/2/' AS HIERARCHYID) );
INSERT INTO #HierarchyDemo VALUES ( CAST ('/1979/4/' AS HIERARCHYID) );
INSERT INTO #HierarchyDemo VALUES ( CAST ('/2012/2/17/' AS HIERARCHYID) );
INSERT INTO #HierarchyDemo VALUES ( CAST ('/1979/4/6/' AS HIERARCHYID) );
SELECT *
FROM #HierarchyDemo;
SELECT *
FROM #HierarchyDemo startingPoint
INNER JOIN #HierarchyDemo parent
ON startingPoint.NodeId.IsDescendantOf(parent.NodeId) = 1
WHERE startingPoint.Description = '/2012/2/17/'
ROLLBACK TRANSACTION