SQL query to get full path from node

Question

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!

Just clarifying - The query you have right now returns only the Root record. You appear to have permission on Child 4 and Child 5 but they do not appear in the result because the intermediate nodes do not have permission...but you wish to be able to see just Child 4 and Child 5 in the query with full path? What about Root? Should you see Root since you do not have permission? — SMM
– SMM, Commented Aug 25, 2016 at 14:15

Serg · Accepted Answer · 2016-08-25 14:16:55Z

3

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);

answered Aug 25, 2016 at 14:16

Serg

22.9k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query to get full path from node

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related