All values from first column matching values in second column as well as not matching values from second column

userColumn	permissionColumn
user1	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

Answer

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.