Finding a Disjoint set between two tables

[Chandra shekar M]

Hi,

 Please find the SQLfiddle link  http://sqlfiddle.com/#!9/89c76/2

create table players(name varchar(32),playerid int);
insert into players values("a",1);
insert into players values("b",2);
insert into players values("c",3);
insert into players values("d",4);
insert into players values("e",5);
insert into players values("f",6);

select * from players;

create table matches(playerid int,game varchar(32));
insert into matches values(1,"game1");
insert into matches values(2,"game1");
insert into matches values(3,"game1");
insert into matches values(1,"game2");
insert into matches values(2,"game2");
insert into matches values(3,"game2");
insert into matches values(4,"game3");
insert into matches values(5,"game2");

select * from matches;

commit;

--This will give same players playing different games
select playerid,game
from matches
group by 1,2

/*
 1. Now say I want to list all the players who wont play a given game, say game1
 2. If player1 is playing game1 and game2 then it should not be returned as he is still
    playing game1 along with other games, in this case example it should return player 4 and 5
 3. If there is a player who is not playing any of the games in this case player 6, should also be returned
    as part of the result. 

*/
    select * from players 
where playerid not in (
    select playerid from matches m 
    where m.game = 'game1') /* 4,5,6*/

select * from players 
where playerid not in (
    select playerid from matches m 
    where m.game = 'game2') /*4,6*/   

select * from players 
where playerid not in (
    select playerid from matches m 
    where m.game = 'game3') /*1,2,3,5,6*/  

How can I do this in a single query for all the games, there can be many types of games. I cant specify the games explicitly, instead, I want to write the query to fetch all the playerids who are not playing a game for each of the game some thing like this

select * from players 
    where playerid not in (
        select playerid from matches m 
        group by game) 

the above query is giving only one record. I need like this

Result:
------------------------
game1 | 4,5,6  
------------------------
game2 | 4,6
------------------------
game3 | 1,2,3,5,6
------------------------
....

[Chandra shekar M]

I need something like a forloop, which can loop over the games and find the players not playing that game. Can I do this in SQL or should I write a procedure

[Jeanne Boyarsky]

Yes, this can be done in raw SQL. It's not particular efficient; doing a loop behind the scenes. But for small datasets, that is fine.

select distinct p.playerid, m.game from players p, matches m where not exists (select * from matches n   where p.playerid = n.playerid      and m.game = n.game ) order by game

The nested query gets the games a player played. If there aren't any for that player/match combo, the outer query includes the data. The distinct is because the cartesian product of the outer query creates duplicate rows.

[Chandra shekar M]

Thanks Jeanne Boyarsky, it helped

[Gerard-Henri Pille]

This may prove to be a bit cheaper than Jeanne's solution:

select G.game, p.name  from (Select distinct Game from matches) As g,      Players p      Where not exists(        select Null from Matches m          Where m.playerid = p.playerid            And m.game = g.game );