CODEWITHSUNDEEP
sqlsql-server
rosuandreimihai
rosuandreimihai

Reputation: 656

SQL query to get full path from node

I have a tree structure table, and I need to find the full path of an element, but in the same time, the elements are restricted by permissions

I have one table tblMapping

map_id  name     parent_id
1       Root     0
2       Child 1  1
3       Child 2  1
4       Child 3  3
5       Child 4  4
6       Child 5  5

And a tblPerms table:

perm_id map_id
1       5
2       6

I am using the following CTE query:

;WITH Hierarchy (map_id, name, parent_id, Path)
AS
(
    SELECT 
        t.map_id, t.name, t.parent_id, 
        CAST(t.name AS varchar(max)) 
    FROM 
        tblMapping t
    LEFT OUTER JOIN 
        tblMapping t1 ON t1.map_id = t.parent_id 
    LEFT OUTER JOIN 
        tblPerms t2 ON t2.map_id = t1.map_id
    WHERE 
        t1.map_id IS NULL

    UNION ALL

    SELECT 
        t.map_id, t.name,t.parent_id, 
        CAST(h.Path + '.' + t.name AS varchar(max)) 
    FROM 
        Hierarchy h
    INNER JOIN 
        tblMapping t ON t.parent_id = h.map_id
)
SELECT Path
FROM Hierarchy
WHERE Path is not null
OPTION (MAXRECURSION 0)

That CTE query will give me the path similiar to Child 4.Child 5

What I would like to have is to be able to have the full path of elements, even if permission is not set for: Root.Child 1.Child 3.Child 4.Child 5

Any idea on how to solve this request?

Thank you!

Upvotes: 1

Views: 1807

Answers (1)

Serg
Serg

Reputation: 22811

First build paths then check permissions

WITH Hierarchy (map_id, name, parent_id, Path)
AS
(
    SELECT 
        t.map_id, t.name, t.parent_id, 
        CAST(t.name AS varchar(max))
    FROM 
        tblMapping t
    LEFT OUTER JOIN 
        tblMapping t1 ON t1.map_id = t.parent_id 
    WHERE 
        t1.map_id IS NULL

    UNION ALL

    SELECT 
        t.map_id, t.name,t.parent_id, 
        CAST(h.Path + '.' + t.name AS varchar(max))
    FROM 
        Hierarchy h
    INNER JOIN 
        tblMapping t ON t.parent_id = h.map_id

)
SELECT Path
FROM Hierarchy h
JOIN tblPerms t2 ON t2.map_id = h.map_id
WHERE Path is not null
OPTION (MAXRECURSION 0);

Upvotes: 3

Related Questions