CODEWITHSUNDEEP
mysqlsql
MasterGberry
MasterGberry

Reputation: 2860

Select characters and group together with joins?

I have the following table setup in mysql:

CREATE TABLE `games_characters` (
  `game_id` int(11) DEFAULT NULL,
  `player_id` int(11) DEFAULT NULL,
  `character_id` int(11) DEFAULT NULL,
  KEY `game_id_key` (`game_id`),
  KEY `character_id_key` (`character_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My objective is to get a game_id where a list of character_ids are all present in this game_id.

An example set of data:

1, 1
1, 2
1, 3
2, 1
2, 2
3, 1
3, 4

Let's say i want to get the game_id where the character_id has 1, 2, and 3. How would I go about making an efficient query? Best idea I have had so far was joining the table to itself multiple times, but i assume there has to be a better way to do this.

Thanks

EDIT: for anyone curious this was the final solution I used as it proved the best query time:

SELECT game_ID
FROM (
    SELECT DISTINCT character_ID, game_ID
    FROM games_Characters
) AS T
WHERE character_ID
IN ( 1, 2, 3 ) 
GROUP BY game_ID
HAVING COUNT( * ) =3

Upvotes: 3

Views: 65

Answers (2)

pyrospade
pyrospade

Reputation: 8070

This ought to do it.

select game_id
from games_characters
where character_id in (1,2,3)
group by game_id
having count(*) = 3

If that's not dynamic enough for you you'll need to add a few more steps.

create temporary table character_ids(id int primary key);

insert into character_ids values (1),(2),(3);

select @count := count(*)
from character_ids;

select gc.game_id
from games_characters as gc
join character_ids as c
    on (gc.character_id = c.id)
group by gc.game_id
having count(*) = @count;

Upvotes: 2

xQbert
xQbert

Reputation: 35343

Select game_ID from games_Characters
where character_ID in (1,2,3)
group by game_ID
having count(*) = 3

the above makes two assumptions
1) you know the characters your looking for
2) game_ID and character_ID are unique

I don't assume you can get the #3 for the count I knnow you can since you know the list of people you're looking for.

Upvotes: 4

Related Questions