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

So when desc_id=0 you want the whole chain (parent, self and children) to be included? E.g. role_group_id=2 type_id=2 map_id=6 and output=B1.B2? But why is the row with B1.B2.B3 included? — NickyvV
– NickyvV, Commented Sep 27, 2016 at 11:04
Actually, the output should be exactly like in the expected table, meaning there should be 2 rows with output B1.B2 and B1.B2.B3, because the query should also include all childs from map_id=6 — rosuandreimihai
– rosuandreimihai, Commented Sep 27, 2016 at 11:06
problem with the cte code you've presented: 'Hierarchy' has fewer columns than were specified in the column list., it doesn't compile for the test code i'm writing — Tanner
– Tanner, Commented Sep 27, 2016 at 11:07
The second row is included because, map_id=7 is a child of map_id=6 — rosuandreimihai
– rosuandreimihai, Commented Sep 27, 2016 at 11:07
first select has 4 columns, second part has 5, please fix this — Tanner
– Tanner, Commented Sep 27, 2016 at 11:08

gofr1 · Accepted Answer · 2016-09-27 12:26:34Z

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

Collectives™ on Stack Overflow

SQL recursive hierarchy

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related