SQL query where ALL records in a join match a condition?

Question

I have what seems to be a simple problem, but can not figure out the proper solution via SQL. I'm using postgresql specifically.

Take the following:

SELECT * FROM users INNER JOIN tags ON (tags.user_id = users.id) WHERE tags.name IN ('word1', 'word2')

This does not do what I need. I want to find users whose tags are ONLY included in the list. If the user has a tag that is not in the list, the user should not be included.

'user1' tags: word1, word2, word3
'user2' tags: word1
'user3' tags: word1, word2

Given: word1 and word2. I want to prepare a query that returns 'user2' and 'user3'. 'user1' is excluded because it has a tag that is not in the list.

Hopefully I made this clear. Thanks for your help!

Answer 1

Relying on COUNT(*) = 2 will require that there can be no duplicates of user_id and name in the tags table. If that's the case, I'd go that route. Otherwise, this should work:

SELECT u.* 
FROM users AS u
WHERE u.id NOT IN (
    SELECT DISTINCT user_id FROM tags WHERE name NOT IN ('word1', 'word2')
) AND EXISTS (SELECT user_id FROM tags WHERE user_id = u.id)