Search code examples
phpmysqlsqlrelational-division

MySQL find row through another table

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.

Solution

  • 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.