SQL - Select parent-childs recursive-CodePudding

I have 3 tables, one is "Permission" that has columns Id, Name and Permission. This is a role/permission table, if Permission column is null, it is a role, otherwise it is a permission. Another table is Permission_Permission, which has IdParentPermission that is the parent Id and IdChildPermission that is the child Id. And the third and last table is Users_Permissions, that relates users with permissions with columns IdUser and IdPermission.

For example:

Permission

Id	Permission	Name
1	DeleteUsers	Can delete users
2	ChangePassword	Can change password
3	NULL	Administrator
4	Listusers	Can view all the users
5	CreateUsers	Can create new users
6	NULL	Operator
7	CreateOffer	Can create a new offer
8	DeleteOffer	Can delete offers
9	NULL	Buyer manager

Permission_Permission

IdParentPermission	IdChildPermission
3	1
3	2
3	4
3	5
6	4
9	7
9	8
3	9

Users_Permissions

IdUser	IdPermission
1	3
1	9
1	1

In this example, the user with Id 1 has the Administrator role, and the administrator role has all the permissions, but also has the "Buyer manager" role inside. It's like a tree with leaves, and a leaf can be a role or a permission, but a leaf can't be parent of his parent leaf.

What is need is a recursive script to retrieve the permissions/roles of a user with all the childs inside in this way.

IdUser	IdPermission	Name	Permission	IdParentPermission

I have tried a lot in SQL using "with" but no luck so far. This has to be recursive in order to get the roles with all the childs of the desired user, but I couldn't make it.

The data that I have in the database is this:

Permission

Id	Permission	Name
1	CanLogIn	Can Log In
2	CanChangePassword	Can Change Password
3	CanViewUsers	Can View Users
4	CanCreateUsers	Can Create Users
5	CanEditUsers	Can Edit Users
6	CanDeleteUsers	Can Delete Users
8	NULL	Family 3
9	NULL	Admin Role
10	NULL	RRHH Role
11	NULL	Test 123

Permission_Permission

IdParentPermission	IdChildPermission
9	1
9	2
9	10
10	1
10	3
10	4
10	5
11	1
10	11

Users_Permissions

IdUser	IdPermission
10	1
10	8
10	9
15	1
15	8
15	9

What I have so far is this:

SET ANSI_NULLS OFF;

declare @IdUser int = 10;
WITH recursivo AS (
        SELECT sp2.IdParentPermission, sp2.IdChildPermission FROM Permission_Permission SP2
        UNION ALL 
        SELECT sp.IdParentPermission, sp.IdChildPermission FROM Permission_Permission sp 
        INNER JOIN recursivo r on r.IdChildPermission = sp.IdParentPermission
    )
    SELECT DISTINCT IdUser, p.Id, r.IdParentPermission, p.Permission, p.Name
    FROM recursivo r 
    INNER JOIN Permission p ON r.IdChildPermission = p.Id
    INNER JOIN Users_Permissions up on up.IdPermission = p.Id and IdUser= @IdUser

Unfortunatelly its not working as expected, only seems to be returning one permission:

IdUser	Id	IdParentPermission	Permission	Name
10	1	9	CanLogIn	Can Log In
10	1	10	CanLogIn	Can Log In
10	1	11	CanLogIn	Can Log In

Expected results:

IdUser	Id	IdParentPermission	Permission	Name
10	1	NULL	CanLogIn	Can Log In
10	8	NULL	NULL	Family 3
10	9	NULL	NULL	Admin Role
10	1	9	CanLogIn	Can Log In
10	2	9	CanChangePassword	Can Change Password
10	10	9	NULL	RRHH Role
10	1	10	CanLogIn	Can Log In
10	3	10	CanViewUsers	Can View Users
10	4	10	CanCreateUsers	Can Create Users
10	5	10	CanEditUsers	Can Edit Users

Edit: renamed tables/columns to match english language

CodePudding user response：

Your problem is that you are starting your recursive CTE one level down. You need to start with the base rows, then recurse from there

DECLARE @IdUser int = 10;

WITH cte AS (
    SELECT up.IdUser, Id = up.IdPermission, IdParentPermission = NULL
    FROM Users_Permissions up
    WHERE up.IdUser = @IdUser

    UNION ALL 
    
    SELECT cte.IdUser, pp.IdChildPermission, pp.IdParentPermission
    FROM cte
    JOIN Permission_Permission pp ON pp.IdParentPermission = cte.Id
)
SELECT
  cte.IdUser,
  cte.Id,
  cte.IdParentPermission,
  p.Permission,
  p.Name
FROM cte
JOIN Permission p ON p.Id = cte.Id;

This result obviously returns multiple results for the same permission. For example, permission 1 inherits from 11 which inherits from 10 which inherits from 9, but this user already has a direct permission for 1.

To resolve this, you could take the row which uses the lowest recursion.

DECLARE @IdUser int = 10;

WITH cte AS (
    SELECT up.IdUser, Id = up.IdPermission, IdParentPermission = NULL, Level = 0
    FROM Users_Permissions up
    WHERE up.IdUser = @IdUser

    UNION ALL 
    
    SELECT cte.IdUser, pp.IdChildPermission, pp.IdParentPermission, cte.Level   1
    FROM cte
    JOIN Permission_Permission pp ON pp.IdParentPermission = cte.Id
),
WithRn AS (
    SELECT
      *,
      rn = ROW_NUMBER() OVER (PARTITION BY IdUser, Id ORDER BY Level, IdParentPermission)
    FROM cte
)
SELECT
  cte.IdUser,
  cte.Id,
  cte.IdParentPermission,
  p.Permission,
  p.Name
FROM WithRN cte
JOIN Permission p ON p.Id = cte.Id
WHERE cte.rn = 1;

db<>fiddle