HierarchyID How to get all Parent from a child

Question

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

Sexpot

This question has also been answered here, and has a rather interesting approach suggested by Simon Ince. #3120360 Cheers, – Desexualize

You should give the columns from the table with some sample data and then provide what you want to achieve as output. It would help people give better answers. Also, if you have already tried something you should give the SQL code. – Newmodel

Answer 1

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

Genteelism

Answer 2

Something like this avoides the CTE

SELECT t1.NodeId.ToString(), t1.Name
    FROM (SELECT * FROM test_table2
        WHERE Name = 'Node 1.1.1') t2
    , test_table2 t1
    WHERE
        t1.NodeId = t2.NodeId OR
        t2.NodeId.IsDescendantOf(t1.NodeId) = 1

Catina

Answer 3

lets assume you have this table:

CREATE TABLE Hierarchy
(
    CompanyNode hierarchyid NOT NULL,
    CompanyId int NOT NULL,
    NodeLevel AS CompanyNode.GetLevel()
    CONSTRAINT PK_Hierarchy PRIMARY KEY NONCLUSTERED (CompanyNode)
)

and you populate it so it hold this data:

CompanyNode CompanyId NodeLevel
0x  1   0
0x58    2   1
0x5AC0  3   2
0x68    100 1
0x6AC0  101 2
0x6AD6  1000    3
0x6AD6B0    10000   4
0x78    20  1
0x7AC0  200 2
0x7AD6  2000    3
0x7AD6B0    20000   4
0x7AD6B580  200000  5
0x7AD6D0    20001   4
0x7ADA  2001    3
0x7ADE  2002    3
0x7B40  201 2
0x7BC0  202 2

and now you want to get all the parents of CompanyId 20001, this is how I did it:

DECLARE @currentLevel smallint

SELECT  @currentLevel = NodeLevel
FROM    Hierarchy
WHERE   CompanyId = 20001;

with tree([Path], [PathName], CompanyId, [Level])
AS
(
SELECT  h.CompanyNode AS [Path],
        h.CompanyNode.ToString() AS [PathName],
        h.CompanyId,
        @currentLevel AS [Level]
FROM    Hierarchy h
WHERE   h.CompanyId = 20001

UNION ALL

SELECT  h.CompanyNode AS [Path],
        h.CompanyNode.ToString() AS [PathName],
        h.CompanyId,
        CAST((t.[Level] - 1) AS smallint) AS [Level]
FROM    Hierarchy h
        INNER JOIN tree t ON
            t.[Path].GetAncestor(1) = h.CompanyNode
    WHERE   h.[NodeLevel] > 0
)   
SELECT * FROM TREE
order by [Path]

you can change the recursive part of the CTE and not filter the top most node of the tree.

hope this helps,

Oded

Ringhals

Answer 4

Declare @hid hierarchyid=0x5D10 -- Child hierarchy id

SELECT
*
FROM 
  dbo.TableName
WHERE 
  @hid.IsDescendantOf(ParentHierarchyId) = 1    

Mola

Does not satisfy the fundamental condition specified by the OP, getting all parents from a child. This gets all children from a parent. – Newmodel