Re: problem selecting from one table where not in another

[laptop alias]

Hm, OK let's see if we can break this down. I've recreated the tables in English, and with some structural adjustments - either for improved efficiency or so I can understand things more easily;

DROP TABLE IF EXISTS player;

CREATE TABLE player
( player_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, player_name VARCHAR(50) NOT NULL
, level TINYINT NOT NULL
);

INSERT INTO player (player_name,level) VALUES
('Anthony Modeste',2),
('Gary Cahill',1),
('Papiss Cisse',1),
('Josh McEachran',2),
('Darron Gibson',3),
('Robbie Keane',2),
('Scott Allan',3),
('Paul Scholes',2),
('Thierry Henry',1),
('Landon Donovan',2);

DROP TABLE IF EXISTS user_player;

CREATE TABLE user_player
(user_id INT NOT NULL
,player_id INT NOT NULL
,PRIMARY KEY(user_id,player_id)
,UNIQUE (player_id)
);

INSERT INTO user_player VALUES
(1001,1),
(1001,2),
(1002,3),
(1003,4),
(1003,5),
(1004,6),
(1004,7),
(1004,8);

DROP TABLE IF EXISTS transfer_list;

CREATE TABLE transfer_list
( player_id INT NOT NULL
, starting_price INT NOT NULL DEFAULT '0'
, PRIMARY KEY (player_id)
);

INSERT INTO transfer_list VALUES (2,84),(3,120),(5,6),(7,5);

SELECT * FROM player;
+-----------+-----------------+-------+
| player_id | player_name     | level |
+-----------+-----------------+-------+
|         1 | Anthony Modeste |     2 |
|         2 | Gary Cahill     |     1 |
|         3 | Papiss Cisse    |     1 |
|         4 | Josh McEachran  |     2 |
|         5 | Darron Gibson   |     3 |
|         6 | Robbie Keane    |     2 |
|         7 | Scott Allan     |     3 |
|         8 | Paul Scholes    |     2 |
|         9 | Thierry Henry   |     2 |
|        10 | Landon Donovan  |     2 |
+-----------+-----------------+-------+


SELECT * FROM user_player;
+---------+-----------+
| user_id | player_id |
+---------+-----------+
|    1001 |         1 |
|    1001 |         2 |
|    1002 |         3 |
|    1003 |         4 |
|    1003 |         5 |
|    1004 |         6 |
|    1004 |         7 |
|    1004 |         8 |
+---------+-----------+

SELECT * FROM transfer_list;
+-----------+----------------+
| player_id | starting_price |
+-----------+----------------+
|         2 |             84 |
|         3 |            120 |
|         5 |              6 |
|         7 |              5 |
+-----------+----------------+

As we can see, there are only two players who are on neither list. We can ilustrate this as follows:

SELECT p.*
     , up.player_id list1
     , tl.player_id list2
  FROM player p
  LEFT
  JOIN user_player up
    ON up.player_id = p.player_id
  LEFT
  JOIN transfer_list tl
    ON tl.player_id = p.player_id;
+-----------+-----------------+-------+-------+-------+
| player_id | player_name     | level | list1 | list2 |
+-----------+-----------------+-------+-------+-------+
|         1 | Anthony Modeste |     2 |     1 |  NULL |
|         2 | Gary Cahill     |     1 |     2 |     2 |
|         3 | Papiss Cisse    |     1 |     3 |     3 |
|         4 | Josh McEachran  |     2 |     4 |  NULL |
|         5 | Darron Gibson   |     3 |     5 |     5 |
|         6 | Robbie Keane    |     2 |     6 |  NULL |
|         7 | Scott Allan     |     3 |     7 |     7 |
|         8 | Paul Scholes    |     2 |     8 |  NULL |
|         9 | Thierry Henry   |     1 |  NULL |  NULL |
|        10 | Landon Donovan  |     2 |  NULL |  NULL |
+-----------+-----------------+-------+-------+-------+

So, where do you want to go from here?