CODEWITHSUNDEEP
sqlsql-servert-sqlselect
mark_h
mark_h

Reputation: 5467

How can I select all users who have a collection of permissions and not one specific permission SQL?

I have the following tables in my SQL Server database;

UserAccount

Username |   UserId
Joe      |        1
Hannah   |        2
Jack     |        3
Jill     |        4

Permission

Permission |   PermissionId
p1         |        1
p2         |        2
p3         |        3
p4         |        4

UserPermission

UserId  |  PermissionId
1       |  1
1       |  2
1       |  3
1       |  4
2       |  1
2       |  2
2       |  4
3       |  1
3       |  2
3       |  3
4       |  2
4       |  3
4       |  4

I want to return the set of user names where the user has permissions p1 and p2 but not permission p3.

Username
Hannah

The closest I got was this;

SELECT ua.Username
FROM UserPermission up
JOIN UserAccount ua ON ua.UserID = up.UserId
JOIN Permission p ON p.PermissionId = up.PermissionId
WHERE p.Name = 'p1' OR p.Name = 'p2' 
GROUP BY up.UserId,ua.Username
HAVING COUNT(up.UserId) = 2

Which only returns users who have permissions p1 and p2 (and it feels like I'm going in completely the wrong direction anyway)

What would be the best way to get the result set shown (and what if I wanted the users who had p1 and p2 but not p3 or p4 for a +1!)?

Thanks.

Upvotes: 1

Views: 114

Answers (3)

gofr1
gofr1

Reputation: 15977

SELECT  ua.UserName
FROM UserPermission up
LEFT JOIN UserAccount ua
    ON ua.UserId = up.UserId
LEFT JOIN Permission p
    ON p.PermissionId = up.PermissionId
GROUP BY ua.UserName
HAVING SUM (CASE WHEN p.Permission IN ('p1','p2') THEN 2 WHEN p.Permission = 'p3' THEN 1 ELSE 0 END) = 4

Output:

UserName
Hannah

Upvotes: 1

marsze
marsze

Reputation: 17035

I use Entity Framework and it gives me the following generated SQL for such a query:

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name]
    FROM [dbo].[Users] AS [Extent1]
    WHERE ( EXISTS (SELECT
        1 AS [C1]
        FROM [dbo].[UserPermissions] AS [Extent2]
        WHERE ([Extent1].[Id] = [Extent2].[User_Id]) AND (1 = [Extent2].[Permission_Id])
    )) AND ( EXISTS (SELECT
        1 AS [C1]
        FROM [dbo].[UserPermissions] AS [Extent3]
        WHERE ([Extent1].[Id] = [Extent3].[User_Id]) AND (2 = [Extent3].[Permission_Id])
    )) AND ( NOT EXISTS (SELECT
        1 AS [C1]
        FROM [dbo].[UserPermissions] AS [Extent4]
        WHERE ([Extent1].[Id] = [Extent4].[User_Id]) AND (3 = [Extent4].[Permission_Id])
    ))

This could be more efficient, I like to use EXISTS myself for better performance. You might want to rewrite it to improve readability and match the column/table names to your database model.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You can continue the idea that you are using. The only difference is removing the where clause and enhancing the having clause:

SELECT u.Username
FROM UserPermission up JOIN
     UserAccount ua
     ON ua.UserID = up.UserId JOIN
     Permission p
     ON p.PermissionId = up.PermissionId
GROUP BY up.UserId,u.Username
HAVING SUM(CASE WHEN p.name = 'p1' THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN p.name = 'p2' THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN p.name IN ('p3', 'p4') THEN 1 ELSE 0 END) = 0;

Upvotes: 2

Related Questions