mysql - Multiple tags search query

Question

I working on a tag based search. I have three tables tag(id,name), tagXmedia(id,tag_id,media_id), and media(id,...). tagXmedia is the mapping table between the tag and media tables. This is a one to many relationship.

I could really use a little direction on how to create an "AND" type of search. For instance I need to be able to search for an entry in the media table that is associated with both the "home" and "hawaii" tags.

I have experimented with MySQL exists such as

SELECT
    tam.media_id
FROM
    tagXmedia tam
    LEFT JOIN tag ON tag.id = tam.tag_id
WHERE
    EXISTS (SELECT * FROM tag  WHERE tag.name = "home")
AND EXISTS (SELECT * FROM tag WHERE tag.name = "hawaii")

Any help on this would really be appreciated.

Answer 1

The following should work.

SELECT media_id
FROM tagXmedia
WHERE tag_id IN (SELECT id FROM tag WHERE name IN ('home','hawaii'))
GROUP BY media_id
HAVING COUNT(tag_id) = 2;

If you wish to have it match more than just two tags, you can easily add them. Just remember to change the 2 in the HAVING clause.

I assumed all the rows in tagXmedia are unique. In case they aren't, you will have to add DISTINCT to the COUNT part.