rosuandreimihai rosuandreimihai - 2 months ago 10
SQL Question

SQL recursive hierarchy

I am struggling to get one recursive CTE to work as desired but still with no chance..
So, I have the following similar table structures:

tblMapping
:

map_id | type_id | name | parent_id
1 1 A1 0
2 1 A2 0
3 1 A3 1
4 1 A4 3
5 2 B1 0
6 2 B2 5
7 2 B3 6
8 1 A5 4
9 2 B4 0


tblRoleGroup
:

role_group_id | type_id | map_id | desc_id
1 1 0 null
1 2 0 null
2 1 3 1
2 2 6 0
3 1 8 1
3 2 9 1


In tblRoleGroup, the
desc_id
field means:

null - allow all (used only in combination with map_id=0)
0 - allow all from parent including parent
1 - allow only current node


Still in tblRoleGroup if
map_id=0
then the query should get all elements from same type_id

The query result should look like this:

role_group_id | type_id | map_id | path
1 1 1 A1
1 1 2 A2
1 1 3 A1.A3
1 1 4 A1.A3.A4
1 1 8 A1.A3.A4.A5
1 2 5 B1
1 2 6 B1.B2
1 2 7 B1.B2.B3
1 2 9 B4
2 1 3 A1.A3
2 2 6 B1.B2
2 2 7 B1.B2.B3
3 1 8 A1.A3.A4.A5
3 2 9 B4


The query below solves only a part of the expected result, but I wasn't able to make it work as the expected result..

WITH Hierarchy(map_id, type_id, name, Path) AS
(
SELECT t.map_id, t.type_id, t.name, CAST(t.name AS varchar(MAX)) AS Expr1
FROM dbo.tblMapping AS t
LEFT JOIN dbo.tblMapping AS t1 ON t1.map_id = t.parent_id
WHERE (t1.parent_id=0)
UNION ALL
SELECT t.map_id, t.type_id, t.name, CAST(h.Path + '.' + t.name AS varchar(MAX)) AS Expr1
FROM Hierarchy AS h
JOIN dbo.tblMapping AS t ON t.parent_id = h.map_id
)
SELECT h.map_id, h.type_id, t.role_group_id, h.Path AS Path
FROM Hierarchy AS h
LEFT JOIN dbo.tblRoleGroup t ON t.map_id = h.map_id


Could someone help me on this?
Thank you

Answer

At first I create a function that brings all descendants of passed map_id:

CREATE FUNCTION mapping (@map_id int)  
RETURNS TABLE  
AS  
RETURN   
(  
    WITH rec AS (
    SELECT  map_id, 
            [type_id], 
            CAST(name as nvarchar(max)) as name, 
            parent_id
    FROM tblMapping
    WHERE map_id = @map_id
    UNION ALL
    SELECT  m.map_id,
            m.[type_id],
            r.name+'.'+m.name,
            m.parent_id
    FROM rec r
    INNER JOIN tblMapping m
    ON m.parent_id = r.map_id
    )

    SELECT *
    FROM rec
);  
GO  

Then run this:

;WITH rec AS (
SELECT  map_id, 
        [type_id], 
        CAST(name as nvarchar(max)) as name, 
        parent_id
FROM tblMapping
WHERE parent_id=0
UNION ALL
SELECT  m.map_id,
        m.[type_id],
        r.name+'.'+m.name,
        m.parent_id
FROM rec r
INNER JOIN tblMapping m
ON m.parent_id = r.map_id
)


SELECT  t.role_group_id,
        r.[type_id],
        r.map_id,
        r.name as [path]
FROM tblRoleGroup t 
CROSS JOIN rec r
WHERE r.[type_id] = CASE WHEN t.desc_id IS NULL AND t.map_id = 0 THEN t.[type_id] ELSE NULL END
   OR r.map_id = CASE WHEN t.desc_id = 1 THEN t.map_id ELSE NULL END
   OR r.map_id IN (
                    SELECT map_id
                    FROM dbo.mapping (CASE WHEN t.desc_id = 0 THEN t.map_id ELSE NULL END)
                    )
ORDER BY role_group_id, r.[type_id], r.map_id

Will give you:

role_group_id   type_id map_id  path
1               1       1       A1
1               1       2       A2
1               1       3       A1.A3
1               1       4       A1.A3.A4
1               1       8       A1.A3.A4.A5
1               2       5       B1
1               2       6       B1.B2
1               2       7       B1.B2.B3
1               2       9       B4
2               1       3       A1.A3
2               2       6       B1.B2
2               2       7       B1.B2.B3
3               1       8       A1.A3.A4.A5
3               2       9       B4