SQL for applying conditions to multiple rows in a

Question

I think I found the answer to my question, I'm just unsure of the syntax, I keep getting SQL errors.

Basically, I want to do the opposite of IN. Take this example:

SELECT * 
  FROM users INNER JOIN 
       tags ON tags.user_id = users.id 
 WHERE tags.name IN ('tag1', 'tag2');

The above will return ANY users that have 'tag1' OR 'tag2'. I want users that have BOTH. They must have both tags to be returned. I'm assuming the keyword ALL should be used, but can't get it to work.

Thanks for your help.

Answer 1

Give this a try

SELECT *
FROM users
INNER JOIN tags ON tags.user_id = users.id
WHERE users.id in
    (
    SELECT user_id
    FROM tags
    WHERE name IN ('tag1', 'tag2')
    GROUP BY user_id
    HAVING COUNT(*) = 2
    )

Answer 2

select * from users u
where 2 = (select count(*) from tags t where t.user_id = u.id and name in ('tag1','tag2'))

Assuming that any given tag can only be present once per user.

Answer 3

Ok, stating the problem again.

"Find users that have entries in the tags table for both tag1 and tag2". This means at least 2 rows in the child tags table for each user table entry

Solution 1: The intersection of "users with tag1" and "users with tag2"

SELECT u.*
FROM 
    users u INNER JOIN 
    (
    SELECT user_id FROM tags WHERE name = 'tag1'
    INTERSECT
    SELECT user_id FROM tags WHERE name = 'tag2'
    ) t ON u.id = t.user_id

Solution 2: EXISTS

SELECT u.*
FROM 
    users u
WHERE 
    EXISTS (SELECT * FROM tags t1 WHERE t1.name = 'tag1'
                     AND u.id = t1.user_id)
    AND
    EXISTS (SELECT * FROM tags t2 WHERE t2.name = 'tag2'
                     AND u.id = t2.user_id)

Solution 3: JOIN

SELECT u.* FROM
   users u
   INNER JOIN
   tags as t1 on t1.user_id = u.id
   INNER JOIN
   tags as t2 on t2.user_id = u.id 
WHERE
   t1.name='tag1' AND t2.name='tag2'

Solution 4: IN

SELECT u.*
FROM 
    users u
WHERE 
    u.id (SELECT t1.user_id FROM tags t1 WHERE t1.name = 'tag1')
    AND
    u.id (SELECT t2.user_id FROM tags t2 WHERE t2.name = 'tag2')

All The EXISTS, INTERSECT and IN should give the same execution plan in SQL Server

Now, these are all for the case where you are looking for 2 tags. As you want more tags, they become cumbersome so use shahkalpesh's solution.

However, I'd modify it so the tags are in a table and no extra OR clauses are needed

SELECT u.*
FROM
    Users u
    Inner join
    tags t ON t.user_id = u.id
    JOIN
    @MyTags mt ON t.name = mt.name
GROUP BY u.*
HAVING count(tags.*) = COUNT(DISTINCT mt.name)

Answer 4

You'll need to check for the existence of two rows, rather than being able to do a simple IN (which will only check the values within each joined record). Maybe something like:

SELECT * 
from users
WHERE EXISTS (SELECT NULL FROM tags WHERE tags.user_id = users.id AND tags.name = 'tag1')
  AND EXISTS (SELECT NULL FROM tags WHERE tags.user_id = users.id AND tags.name = 'tag2');

Answer 5

What about

SELECT * FROM users, tags WHERE tags.user_id = users.user_id AND tags.name = 'tag1'
INTERSECT
SELECT * FROM users, tags WHERE tags.user_id = users.user_id AND tags.name = 'tag2'

Answer 6

I would do exactly what you are doing first, because that gets a list of all users with 'tag1' and a list of all users with 'tag2', but in the same response obviously. So, we have to add some more:

Do a group by users (or users.id) and then having count(*) == 2. That will group duplicate users (which means those with both tag1 and tag2) and then the having-part will remove the ones with just one of the two tags.

This solution avoids adding yet another join-statement, but honestly I'm not sure which is faster. People, feel free to comment on the performance-part :)

EDIT: Just to make it easier to try out, here's the whole thing:

SELECT * 
FROM users INNER JOIN 
     tags ON tags.user_id = users.id 
WHERE tags.name = 'tag1' OR tags.name = 'tag2'
GROUP BY users.id
HAVING COUNT(*) = 2