SQL SELECT with m:n relationship

Question

I have m:n relationship between users and tags. One user can have m tags, and one tag can belong to n users. Tables look something like this:

USER:
ID
USER_NAME

USER_HAS_TAG:
USER_ID
TAG_ID

TAG:
ID
TAG_NAME

Let's say that I need to select all users, who have tags "apple", "orange" AND "banana". What would be the most effective way to accomplish this using SQL (MySQL DB)?

Answer 1

SELECT  u.*
FROM    (
        SELECT  user_id
        FROM    tag t
        JOIN    user_has_tag uht
        ON      uht.tag_id = t.id
        WHERE   tag_name IN ('apple', 'orange', 'banana')
        GROUP BY
                user_id
        HAVING  COUNT(*) = 3
        ) q
JOIN    user u
ON      u.id = q.user_id

By removing HAVING COUNT(*), you get OR instead of AND (though it will not be the most efficient way)

By replacing 3 with 2, you get users that have exactly two of three tags defined.

By replacing = 3 with >= 2, you get users that have at least two of three tags defined.

Answer 2

In addition to the other good answers, it's also possible to check the condition in a WHERE clause:

select *
from user u
where 3 = (
    select count(distinct t.id)
    from user_has_tag uht
    inner join tag t on t.id = uht.tag_id
    where t.name in ('apple', 'orange', 'banana') 
    and uht.user_id = u.userid
)

The count(distinct ...) makes sure a tag is counted only once, even if the user has multiple 'banana' tags.

By the way, the site fruitoverflow.com is not yet registered :)