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!

possible duplicate of [SQL, how to match ALL?](http://stackoverflow.com/questions/4763143/sql-how-to-match-all) — Conrad Frix, Jan 21 '11 at 23:00
No, my previous question required finding users that had all of the tags, and that was the only requirement. This one requires users that do have tags outside of the list. Different problem. — Binary Logic, Jan 21 '11 at 23:13

nybbler · Accepted Answer · 2011-01-28T07:48:27.413

7

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)

edited Jan 28 '11 at 07:48

answered Jan 21 '11 at 23:03

nybbler

4,793
28
23

2

This *may* include users with no tags. – Andriy M Jan 28 '11 at 07:38
@Andriy M: You are correct. I've updated my answer to account for this now. Thank you for pointing it out. – nybbler Jan 28 '11 at 07:49

score 1 · Answer 2 · answered Oct 24 '13 at 16:25

1

SELECT distinct users.id
FROM users 
INNER JOIN tags ON (tags.user_id = users.id) 
group by users.id 
having count(*) = 2 
and min(tags.name) = 'word1'
and max(tags.name) = 'word2'

answered Oct 24 '13 at 16:25

DragonMoon

415
1
4
14

1

Could you provide some explanation about why your query is better than what has already been posted? – Theresa Oct 24 '13 at 16:47

Quassnoi · Answer 3 · 2011-01-21T23:18:32.143

1

SELECT  user_id
FROM    users
WHERE   id IN
        (
        SELECT  user_id
        FROM    tags
        )
        AND id NOT IN
        (
        SELECT  user_id
        FROM    tags
        WHERE   name NOT IN ('word1', 'word2')
        )

or

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

edited Jan 21 '11 at 23:18

answered Jan 21 '11 at 22:52

Quassnoi

413,100
91
616
614

This will return all users that match both words, without checking if they do not match other words. This does not answer the question. – GolezTrol Jan 21 '11 at 23:04

GolezTrol · Answer 4 · 2011-01-21T23:03:29.733

0

To get all users that don't have a tag that is not in the list, use the query below. Could be that users are returned that have no tag or only one tag matching the words, but I understand that is the desired functionality.

SELECT
  u.*
FROM
  users u
  LEFT JOIN tags t 
    ON t.user_id = u.userid AND
       t.name NOT IN ('word1', 'word2')
WHERE
  t.user_id IS NULL

edited Jan 21 '11 at 23:03

answered Jan 21 '11 at 22:58

GolezTrol

114,394
18
182
210

score 0 · Answer 5 · answered Jan 28 '11 at 07:46

0

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

answered Jan 28 '11 at 07:46

Andriy M

76,112
17
94
154

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

5 Answers5

Linked