MySQL通过另一个表查找行

I have two tables:

我有两个表:

game

游戏

`id`        INT(11)

game_tags

game_tags

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

game_tags.game = game.id

game_tags。游戏= 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:

我很讨厌MySQL，所以我的问题是:我想要找到哪些游戏有一定数量的tag_id。如果我有4个tag_id(3,5,7,11)，我想通过查看game_tags表，找到哪些游戏会有这4个标签。这里有一个例子，我的意思是:

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.

我知道我解释了这个可怕的事情(我想不出来怎么说)，所以如果你有任何问题，请留下评论。

4 个解决方案

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

您可以使用group by和带有子句以及Bassam的查询来确保为给定的游戏找到了所有四个id。

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.

请注意，这是有效的，因为在聚合计数(不同的…)运行之后，having子句运行，而where子句没有这个信息。

#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

#3

you can do four inner joins and add four where condition like

您可以执行4个内部连接，并添加4个where条件like

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

t1。tag_id = 1和t2。tag_id = 2和....

this gives you what you want. but there may be better performing way

这就是你想要的。但可能会有更好的表演方式。

#4

Yeah, this is a funny approach. Bad query. But also can be done like this. 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);

#1