Excluding pairs of values occurring non-exclusively

Question

This question is surprisingly hard to describe using English, so I'll give some examples.

I have a table named Games. Each game consists of game numbers and players, represented in two columns: GameNum, PlayerNum.

My question is, I want to select pairs of players who have only played in games with each other, and no one else.

Here is some sample data:

GameNum PlayerNum
1       100
1       101
2       102
2       103
3       102
3       104
4       105
4       106
5       106
5       107
6       100
6       101

I'm looking to return the results:

PlayerNum1 PlayerNum2
100        101

This is because we can see that players 100 and 101 are the only players to have played games with one another, and no one else. 102 has also played with 104, so we exclude 102 and 104. And while 105 has only played a game with 106, 106 has also played a game with 107, so we exclude both players 105 and 106 (and hence 107) from the results. This leaves us with only players 100 and 101.

Each GameNum will only occur twice in the table (i.e. each game will always have exactly two players). Also note that we don't care if players have played multiple games together (e.g. GameNum 1 and 6) - provided that they have only played these games with each other.

I have tried something like the following query using min/max, but I can't figure out how to exclude players who have played games with other players.

SELECT *
FROM
(
    SELECT AU1.PlayerNum AS PlayerNum1, AU2.PlayerNum AS PlayerNum2
    FROM
    (
      SELECT GameNum, MIN(PlayerNum) AS PlayerNum
      FROM GAMES
      GROUP BY GameNum
      HAVING count(GameNum) = 2
    ) AU1
    INNER JOIN 
    (
      SELECT GameNum, MAX(PlayerNum) AS PlayerNum
      FROM GAMES
      GROUP BY GameNum
      HAVING count(GameNum) = 2
    ) AU2
    ON AU2.GameNum = AU1.GameNum
) T2
GROUP BY T2.PlayerNum1, T2.PlayerNum2
ORDER BY T2.PlayerNum1, T2.PlayerNum2;

Many thanks! :)

EDIT: Here is the query to create a basic version of the table with the above data:

CREATE TABLE Games
(
    GameNum int,
    PlayerNum int
);

INSERT INTO Games (GameNum, PlayerNum) VALUES (1, 100);
INSERT INTO Games (GameNum, PlayerNum) VALUES (1, 101);
INSERT INTO Games (GameNum, PlayerNum) VALUES (2, 102);
INSERT INTO Games (GameNum, PlayerNum) VALUES (2, 103);
INSERT INTO Games (GameNum, PlayerNum) VALUES (3, 102);
INSERT INTO Games (GameNum, PlayerNum) VALUES (3, 104);
INSERT INTO Games (GameNum, PlayerNum) VALUES (4, 105);
INSERT INTO Games (GameNum, PlayerNum) VALUES (4, 106);
INSERT INTO Games (GameNum, PlayerNum) VALUES (5, 106);
INSERT INTO Games (GameNum, PlayerNum) VALUES (5, 107);
INSERT INTO Games (GameNum, PlayerNum) VALUES (6, 100);
INSERT INTO Games (GameNum, PlayerNum) VALUES (6, 101);
INSERT INTO Games (GameNum, PlayerNum) VALUES (5, 107);
INSERT INTO Games (GameNum, PlayerNum) VALUES (6, 100);
INSERT INTO Games (GameNum, PlayerNum) VALUES (6, 101);

Answer 1

try something like this (mysql syntax):

select distinct least(t1.p1, t2.p2), greatest(t1.p1, t2.p2)
from
(
 select p1, max(p2) as p2
 from (select min(PlayerNum) as p1, max(PlayerNum) as p2 from GAMES group by GameNum union select max(PlayerNum) as p1, min(PlayerNum) as p2 from GAMES group by GameNum) as q1
 group by q1.p1
 having count(distinct p2)=1
) as t1
,
(
 select min(p1) as p1, p2
 from (select min(PlayerNum) as p1, max(PlayerNum) as p2 from GAMES group by GameNum union select max(PlayerNum) as p1, min(PlayerNum) as p2 from GAMES group by GameNum) as q2
 group by q2.p2
 having count(distinct p1)=1
) as t2
where t1.p1=t2.p1 and t1.p2=t2.p2

http://sqlfiddle.com/#!2/4d9c4/9

main idea:

• select all player1 who had played only with 1 another player
• select all player2 who had played only with 1 another player

join both sets to find out players who played only with each other

Answer 2

with t as (
  select distinct min(playernum) a, max(playernum) b 
  from games group by gamenum
)
select x.a PlayerNum1, x.b PlayerNum2
from t x left join t y 
on not (x.a=y.a and x.b=y.b) and (y.a in (x.a,x.b) or y.b in (x.a,x.b))
where y.a is null

fiddle