CODEWITHSUNDEEP
mysqlselectjoin
Kaya Toast
Kaya Toast

Reputation: 5503

MySQL query for pairs

How can I select all players that have not been already paired with 'alpha' ? (output = 'gamma'). It should work even when the pairs table is empty. 

table: players
+----------+-------+
| playerID | name  |
+----------+-------+
|        1 | alpha |
|        2 | beta  |
|        3 | gamma |
+----------+-------+

table: pairs
+---------+---------+
| player1 | player2 |
+---------+---------+
|       2 |       3 |
|       1 |       2 |
+---------+---------+

I have been struggling for several hours now. For e.g. if I do this

SELECT p.*, r.*
FROM players p
JOIN pairs r
    ON (player1 = playerID) OR (player2 = playerID)
WHERE
    ((r.player1 != 1) AND (r.player2 != 1));

the output is 'beta' and 'gamma'. In the join, 'beta' appears twice (once each for its pairing with 'alpha' and 'gamma'). And the WHERE condition eliminates one row of 'beta'. What I want is for all rows of 'beta' to be eliminated. I am new to this and tried various combinations of GROUP BY, HAVING etc. I'm not able to get it to work.

SQL Fiddle here.

Upvotes: 1

Views: 1495

Answers (4)

Kaya Toast
Kaya Toast

Reputation: 5503

An alternate solution, without using "NOT IN".

Approach: (all users) - (all existing opponents)

This works even when (all existing opponents) IS NULL.

SELECT p.* FROM players p
LEFT JOIN (
    SELECT CASE
            WHEN (player1 = 1) THEN player2
            WHEN (player2 = 1) THEN player1
        END AS opponentID
    FROM pairs) existingOpponents
ON (p.playerID = existingOpponents.opponentID)
WHERE (existingOpponents.opponentID IS NULL) AND 
    (p.playerID != 1);

Upvotes: 0

AdamMc331
AdamMc331

Reputation: 16690

I would break this down into smaller bits and piece it together. Start by getting the id of the player who is alpha:

SELECT playerID
FROM players
WHERE name = 'alpha';

The next step would be to figure out how to exclude. I'd get a list of all players who were paired with player 1. I did this by selecting all player2 values where player1 is 'alpha' and all player1 values where player2 is 'alpha' like this:

SELECT p.player2
FROM pairs p
JOIN(
  SELECT playerID
  FROM players
  WHERE name = 'alpha') b ON b.playerID = p.player1
UNION
SELECT p.player1
FROM pairs p
JOIN(
  SELECT playerID
  FROM players
  WHERE name = 'alpha') b ON b.playerID = p.player2;

Once you've done that, the only step that remains is to pull from player where the person is 1) not alpha and 2) not in the above list:

SELECT *
FROM players
WHERE playerID NOT IN(
  SELECT playerID
  FROM players
  WHERE name = 'alpha') 
AND playerID NOT IN(
  SELECT p.player2
  FROM pairs p
  JOIN(
    SELECT playerID
    FROM players
    WHERE name = 'alpha') b ON b.playerID = p.player1
  UNION
  SELECT p.player1
  FROM pairs p
  JOIN(
    SELECT playerID
    FROM players
    WHERE name = 'alpha') b ON b.playerID = p.player2);

SQL Fiddle example here.

Upvotes: 3

John
John

Reputation: 3996

This should do it:

drop table pair;
drop table player;

create table player (
    id int,
    name varchar(32)
);

create table pair (
    id1 int,
    id2 int
);

insert into player values (1, 'Andy');
insert into player values (2, 'Bob');
insert into player values (3, 'Carl');
insert into player values (4, 'Dave');

insert into pair values (2, 3);
insert into pair values (1, 2);
insert into pair values (3, 1);

select * from player where id not in (
select
    if(pair.id1 = player.id, pair.id2, pair.id1) as other_player
from 
    pair
    join player on pair.id1 = player.id or pair.id2 = player.id
where
    player.name = 'Andy'
) 
;

Upvotes: 1

J A
J A

Reputation: 1766

Something like this should work for this scenario. 

SELECT
    pl.*
FROM 
    players as pl
WHERE 
    pl.playerID NOT IN 
    (
      SELECT 
          p.player2
      FROM
          pairs AS p
      INNER JOIN 
          players plr ON plr.playerID = p.player1
      WHERE 
          plr.name='alpha'

    )

In case player1 and player 2 columns in pairs table changes position, you may have to use a CASE inside the subquery.

Upvotes: 1

Related Questions