Find players that played both games

Question

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.

Answer 1

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.