Find players that played both games

user_10253229 0
Viewed 89

I want to get the playerid of those who played both chess and checkers. I have a table that's like this:

playerid   game         website
a1         chess        www.abc.com
a2         checkers     www.cba.com
a1         checkers     www.abc.com
b2         chess        www.abc.com
b1         chess        www.abc.com
a3         checkers     www.aaa.com
b2         checkers     www.abc.com

Desired output (a3 and b1 should be excluded)

a1
b2

select game, playerid, website
from player_db
where (game= 'chess' or game= 'checkers') and website='abc.com'
group by playerid ;

This is my SQL, but it doesn't seem to be getting both of the games -- it selects either chess or checkers.

group-by mysql sql
6 Answers

The problem when using OR is that also every entry will be found that satisfies only one of your two conditions, but you want to get only those which meet both conditions. The problem when using AND (this answer seems to have been deleted again) will be you will get no rows since a game can't equal "chess" and "checker" the same time. So, this means you will need two queries and to check which players occur in both of them, one checking for game = "chess" and one for game="checker". There are different options to do this. One possibility is to use an IN clause:

SELECT DISTINCT playerid
FROM daily_player
WHERE playerid IN (SELECT playerid FROM daily_player WHERE game = 'chess')
AND playerid IN (SELECT playerid FROM daily_player WHERE game = 'checkers')
AND website='abc.com';

Another way is using EXISTS:

SELECT DISTINCT playerid
FROM daily_player d1
WHERE EXISTS (SELECT 1 FROM daily_player WHERE game = 'chess' AND playerid = d1.playerid)
AND EXISTS (SELECT 1 FROM daily_player WHERE game = 'checkers' AND playerid = d1.playerid) 
AND website='abc.com'

Please note that your desired outcome of "a1 b2" can only be achieved when using DISTINCT and not selecting differing columns like game etc. In case you want to still select them you can of course do this:

SELECT DISTINCT playerid,game,website
FROM daily_player
WHERE playerid IN (SELECT playerid FROM daily_player WHERE game = 'chess')
AND playerid IN (SELECT playerid FROM daily_player WHERE game = 'checkers')
AND website='abc.com';

But then you will not get the result you have shown, but in this case four lines instead of 2. If you remove the game of the select above, you will get two rows again because the website does not differ.

user_187948260

A little long query with multiple sub queries may do.

SELECT playerid FROM daily_player a
WHERE 
(SELECT COUNT(*) FROM daily_player WHERE game ='chess' AND playerid=a.`playerid`)=1 
AND
(SELECT COUNT(*) FROM daily_player WHERE game ='checkers' AND playerid=a.`playerid`)=1
AND website='www.abc.com'
GROUP BY playerid;

RESULT

playerid  
----------
a1        
b2

Explanation:

  1. 1st Subquery : Evaluate if the player played the game chess.
  2. 2nd Subquery : Evaluate if the player played the game checkers.
  3. Check if both subqueries are TRUE by using AND.
  4. Add another AND to evaluate the website.

Note : If every player can play a single game multiple times you can replace the =1 with >=1.

user_153523250 
select DISTINCT t1.player_id from daily_player as t1
        where exists (select 1 from daily_player as t2 where t1.player_id = t2.player_id and game = 'chess')
        and exists (select 1 from daily_player as t3 where t1.player_id = t3.player_id and game = 'checkers')
        and  website='abc.com'
user_168347660

I usually find duplicates in a column by "HAVING ".

if you have just two games this is shortest way.

SELECT playerid
FROM daily_player
GROUP BY playerid 
HAVING COUNT(playerid ) > 1;
user_152163190

Could go for a join function if you don't want a really long query. Something like:

create table chess as select * from daily_player where game = 'chess';
create table checkers as select * from daily_player where game = 'checkers';

select chess.playerid from chess inner join checkers
on chess.playerid = checkers.playerid;
user_144862060

If you want to fetch playerid with who played both chess and checkers, then the query will be:

SELECT playerid
FROM daily_player
WHERE game in ('checkers', 'chess') && website = 'www.abc.com' GROUP by playerid HAVING COUNT(*)=2;
user_191266690
Related