| userColumn | permissionColumn |
|---|---|
| userl | permission1 |
| user1 | permission2 |
| user2 | permission1 |
| user3 | permission1 |
| user3 | permission3 |
| user4 | permission1 |
As cited in the subject line, I need to fetch all those users who have permissions 'permission1' but at the same time, I have to make sure that it doesn't have the other permissions like 'permission2' and 'permission3'.
How can I achieve this with MySQL? Will it work with just a single query or do we need an inner join or nested query?
Desired Result:
| userColumn |
|---|
| user2 |
| user4 |
CodePudding user response：
SELECT user
FROM table
GROUP BY user
HAVING NOT SUM(permission <> 'permission1');
permission <> 'permission1' returns 0 for 'permission1' and 1 otherwise.
SUM() by fact counts the amount of non-'permission1' values. We need the rows where there is no 'permission1', i.e. where this sum is zero.
NOT inverts SUM() result - i.e. converts zero to 1 and non-zero to 0. As a result we obtain only needed rows.
The query assumes that permission column cannot be NULL.
CodePudding user response：
One option would be using conditional aggregation while grouping by user in order to check whether number of permissions of values permission1 equals to the number of all rows per each user such as
SELECT user
FROM UserPermissions
GROUP BY user
HAVING COUNT(*) = SUM(CASE WHEN permission = 'permission1' THEN 1 END)