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.

Check this question for more than 10 ways to achieve what you want: http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation — ypercubeᵀᴹ, Dec 28 '11 at 15:32

score 4 · Accepted Answer · answered Dec 28 '11 at 05:20

4

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.

answered Dec 28 '11 at 05:20

Adam Wagner

15,469
7
52
66

unfortunately we won't do it this way, group by will complain about games.* because the columns are not in an aggregate function. it will have to be done in a sub select – Bassam Mehanni Dec 28 '11 at 13:25
only if there are additional columns in the game table. OP stated the game table only has 1 column: `id`. Even so, I don't think mysql complains about this. But, if it does, you are correct, a sub-select would be required. – Adam Wagner Dec 28 '11 at 14:29
Well, the game table has like 10 columns, but I only posted the one column that I thought you would need for this query. – Matt Dec 28 '11 at 16:58

Bassam Mehanni · Answer 2 · 2011-12-28T14:41:19.073

3

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

edited Dec 28 '11 at 14:41

answered Dec 28 '11 at 04:40

Bassam Mehanni

14,796
2
33
41

I dont think this is quite correct, as it will bring back entries which contain any of the four specified tags, whereas the OP asked for **I want to be able to find what games will have all four** – Adriaan Stander Dec 28 '11 at 04:43
Thanks, but as astander said, I need to be able to find what games have **all four** – Matt Dec 28 '11 at 04:48
@BassamMehanni Does MySQL let you say `count(distinct *)`? I don't have a copy handy, but sqlite complains. – Adam Wagner Dec 28 '11 at 05:41
@AdamWagner I removed distinct, we shouldn't need it in this query – Bassam Mehanni Dec 28 '11 at 13:23
True, if no more joins are added to the query. (and if there is a unique/primary constraint on `game_tags.game, game_tags.tag_id`) – Adam Wagner Dec 28 '11 at 14:27
@AdamWagner good point about a possible missing unique constraint – Bassam Mehanni Dec 28 '11 at 14:42
I have game.id as the primary key. Do I need to make anything in the game_tags table primary/unique? – Matt Dec 28 '11 at 16:59
you need to add a unique constraint to your game_tags table so that game and tag_id combination is not repeated more than once – Bassam Mehanni Dec 28 '11 at 17:03
I voted your answer up, but since Adam's answer runs slightly faster, I picked his answer for the question. Anyways, thanks :D – Matt Dec 28 '11 at 17:22
No problem, glad I can help, I am just not sure how you gonna be able to use his without the inner join when you have more columns on the game table but I'll leave that up to you to figure out :D – Bassam Mehanni Dec 28 '11 at 17:36
Well, the actually game table is like game.name, game.desc, game.url, etc. Will that effect it? Maybe I misunderstood the column discussion. – Matt Dec 28 '11 at 17:48
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/6201/discussion-between-bassam-mehanni-and-matt) – Bassam Mehanni Dec 28 '11 at 17:49

score 0 · Answer 3 · answered Dec 28 '11 at 05:28

0

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

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

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

answered Dec 28 '11 at 05:28

halil

1,789
15
18

score 0 · Answer 4 · answered Dec 28 '11 at 05:53

0

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);

answered Dec 28 '11 at 05:53

lqez

2,898
5
25
55

And what happens if `tag_id=37011` ? – ypercubeᵀᴹ Dec 28 '11 at 15:29

MySQL find row through another table

4 Answers4