MySQL find row through another table

Question

I have two tables:

game

`id`        INT(11)

game_tags

`game`      INT(11)
`tag_id`    INT(11)

game_tags.game = game.id

I am horrible with MySQL, so here is my question: I want to be able to find what games have a certain amount of tag_id's. So if I have four tag_id's (3, 5, 7, 11), I want to be able to find what games will have all four of those tags by looking through the game_tags table. Here is an example of what I mean:

pseudo-MySQL:

SELECT *
FROM `games`
WHERE (search through game_tags table and find which rows have the same `game` field and all of the tag_id's that I need to search for)
LIMIT 0, 15

I know I explained this horrible (couldn't word it like in my mind), so if you have any questions, just leave a comment.

Answer 1

You can use group by and having clauses along with Bassam's query to ensure you have found all four ids for a given game.

select
    game.*
from game
    join game_tags on game.id = game_tags.game
where
    tag_id in (3, 5, 7, 11)
group by
    game.id
having 
    count(distinct tag_id) = 4;

Note that this works because the having clause runs after the aggregation count(distinct ...) runs, whereas a where clause does not have this info.

Answer 2

SELECT games.*
FROM games
     INNER JOIN 
     (SELECT game, COUNT(DISTINCT tag_id) AS gameCnt
      FROM game_tags
      WHERE tag_id in (3, 5, 7, 11)
      GROUP BY game) t on games.id = game
WHERE gameCnt = 4