SQL recursive hierarchy

Question

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 1

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