Mysql – How to filter query results based on whether they have ALL values from another table

MySQLmysql-5.7relational-division

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?

Best Answer

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.

Related Question