Re: problem selecting from one table where not in another

[laptop alias]

>it needs to store the real player id as well as the user player id

Under what circumstances could these be different?

>Also the user_player and transfer_list tables need to store the league id.

This HAS to be a structural error! Also, from the little I understand about football, it makes no logical sense either.

For simplicity, let's store the league_id in the player table. This is also wrong, but does at least avoid the inevitable redundancy of your solution...

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
, league_id INT NOT NULL
);

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

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 p.*
     , up.player_id list1
     , tl.player_id list2
     , p.league_id = 101
  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 | league_id | list1 | list2 | p.league_id = 101 |
+-----------+-----------------+-------+-----------+-------+-------+-------------------+
|         1 | Anthony Modeste |     2 |       101 |     1 |  NULL |                 1 |
|         2 | Gary Cahill     |     1 |       101 |     2 |     2 |                 1 |
|         3 | Papiss Cisse    |     1 |       101 |     3 |     3 |                 1 |
|         4 | Josh McEachran  |     2 |       101 |     4 |  NULL |                 1 |
|         5 | Darron Gibson   |     3 |       102 |     5 |     5 |                 0 |
|         6 | Robbie Keane    |     2 |       101 |     6 |  NULL |                 1 |
|         7 | Scott Allan     |     3 |       102 |     7 |     7 |                 0 |
|         8 | Paul Scholes    |     2 |       102 |     8 |  NULL |                 0 |
|         9 | Thierry Henry   |     1 |       101 |  NULL |  NULL |                 1 |
|        10 | Landon Donovan  |     2 |       101 |  NULL |  NULL |                 1 |
+-----------+-----------------+-------+-----------+-------+-------+-------------------+