mark_h mark_h - 6 months ago 17
SQL Question

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.

Answer

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;