How can I filter query results based on whether they have ALL values from another table?

Question

For example, some actions that users can do, require a set of permissions.

Users are stored in the Users table:

UserId
1
2

and permissions in the Permissions table. Users can have permission A, B, or both.

UserId	Permission
1	A
1	B
2	A

Required permissions are stored in RequiredPermissions. From this, the POST action requires both permissions A and B

ActionType	Permission
POST	A
POST	B
GET	B

I want to query all users that can POST (user 1). This query would give users that have any permissions (that is, user 1 and 2):

select distinct u.UserId
from Users as u
join RequiredPermissions as rp
  on rp.ActionType = "POST"
join Permissions as p
  on u.UserId = p.UserId

How do I query users which have both permissions A and B with this schema?

Answer 1

Relational division is a less common type of query than the more typical joins and unions. It's a little more awkward in SQL.

You want to know the users who have all the permissions required by POST.

Another way to state this is to return users for whom no permission required for POST is missing from their permissions.

SELECT u.UserID
FROM Users AS u
WHERE NOT EXISTS (
  SELECT *
  FROM RequiredPermissions AS rp
  LEFT OUTER JOIN Permissions AS p
    ON rp.Permission = p.Permission AND u.UserId = p.UserId
  WHERE rp.ActionType = 'POST'
    AND p.UserId IS NULL)

The way it works is that the OUTER JOIN will make p.* NULL if there is no match. So if the outer query wants there to be no case where a required permission has no match for the respective user, then that user must have all the required permissions.

This automatically handles actions with 3 or more permissions. The other solutions for relational division that I've seen require you to make code changes to your query for the number of items you are searching for.

Answer 2

There are basically two ways to do relational division in SQL. The first version is described by Bill Karwin and uses an extension of De Morgan's laws into predicate logic:

FORALL p(x) == NOT EXISTS NOT p(x) 

The other version described by nbk compares the cardinality of the domain with the cardinality of each user. Something like:

SELECT userid
FROM Permissions
JOIN RequiredPermissions
    USING (permission)
GROUP BY userid
HAVING COUNT(distinct ActionType) 
     = (SELECT COUNT(distinct ActionType) FROM RequiredPermissions);

The latter is a bit easier to generalize into queries like "Which users have more than X percent of ...."

SELECT userid
FROM Permissions
JOIN RequiredPermissions
    USING (permission)
GROUP BY userid
HAVING COUNT(distinct ActionType) 
     >= 0.4*(SELECT COUNT(distinct ActionType) FROM RequiredPermissions);

Answer 3

You can sum up how many permissions a user has and check it against a number

CREATE TABLE Permissions (
  `UserId` INTEGER,
  `Permission` VARCHAR(1)
);

INSERT INTO Permissions
  (`UserId`, `Permission`)
VALUES
  ('1', 'A'),
  ('1', 'B'),
  ('2', 'A');

CREATE TABLE RequiredPermissions (
  `ActionType` VARCHAR(4),
  `Permission` VARCHAR(1)
);

INSERT INTO RequiredPermissions
  (`ActionType`, `Permission`)
VALUES
  ('POST', 'A'),
  ('POST', 'B'),
  ('GET', 'B');

SELECT DISTINCT `UserId` FROM Permissions WHERE `Permission` iN
(SELECT `Permission` FROM RequiredPermissions WHERE `ActionType` = 'POST')

| UserId |
| -----: |
|      1 |
|      2 |

SELECT `UserId`
FROM Permissions 
GROUP BY `UserId`
HAVING SUM((`Permission` =  'A') + (`Permission` =  'B')) = 2

| UserId |
| -----: |
|      1 |

db<>fiddle here