MySQL finds a row through another table

Question

I have two tables:

the game

`id` INT(11) 

game_tags

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

game_tags.game = game.id

I am terrible with MySQL, so here is my question: I want to find that games has a certain amount of tag_id . Therefore, if I have four tag_id (3, 5, 7, 11), I want to find which games will have all four of these tags by looking at 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 that I need to search for) LIMIT 0, 15 

I know that I explained this terrible (I could not say it, as in my opinion), so if you have any questions, just leave a comment.

Answer 1

You can use the group by and having clauses along with the Bassam query to make sure you find all four identifiers for this 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 run after the aggregation of count(distinct ...) , while the where clause does not contain this information.

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 

Answer 3

you can make four internal joins and add four conditions, for example

t1.tag_id = 1 and t2.tag_id = 2 and ....

it gives you what you want. but there may be a more efficient way

Answer 4

Yes, this is a fun approach. Bad request. But you can also do so. Just for fun!

 SELECT game, BIT_OR(pow(2,tag_id)) AS tags FROM game_tags GROUP BY game HAVING tags = pow(2,3) + pow(2,5) + pow(2,7) + pow(2,11);