Select symbols and group along with unions?
I have the following table 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 goal is to get the game_id where the list of character-characters is present in this game.
Example dataset:
1, 1 1, 2 1, 3 2, 1 2, 2 3, 1 3, 4
Let's say I want to get game_id where character_id is 1, 2 and 3. How can I make an efficient query? The best idea I've had so far was to join the table to itself multiple times, but I guess there must be a better way to do this.
thank
EDIT: For someone curious, this was the final solution I used since this turned out to be 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
+3
source to share
2 answers
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 am not suggesting that you can get # 3 for the account that I know as you know the list of people you are looking for.
+4
source to share
This should do it.
select game_id
from games_characters
where character_id in (1,2,3)
group by game_id
having count(*) = 3
If you are not dynamic enough, you 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;
+2
source to share