Aha, OK, now I see how it works.
So users should actually select players from a 'league_player` table...
Oh, let's start again...
DROP TABLE IF EXISTS player;
DROP TABLE IF EXISTS user_player;
DROP TABLE IF EXISTS transfer_list;
DROP TABLE IF EXISTS league_player;
CREATE TABLE league_player
( league_player_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, league_id INT NOT NULL
, player_id INT NOT NULL
, player_name VARCHAR(50) NOT NULL -- this could still be stored in a player table
, level TINYINT NOT NULL
, UNIQUE(league_id,player_id)
);
INSERT INTO league_player (player_id,player_name,level,league_id) VALUES
(1,'Anthony Modeste',2,101),
(2,'Gary Cahill',1,101),
(3,'Papiss Cisse',1,101),
(4,'Josh McEachran',2,101),
(5,'Darron Gibson',3,102),
(6,'Robbie Keane',2,101),
(7,'Robbie Keane',2,102),
(8,'Scott Allan',3,102),
(9,'Paul Scholes',2,102),
(10,'Thierry Henry',1,101),
(11,'Thierry Henry',1,102),
(12,'Landon Donovan',2,101);
DROP TABLE IF EXISTS user_league_player;
CREATE TABLE user_league_player
(user_id INT NOT NULL
,league_player_id INT NOT NULL
,PRIMARY KEY(user_id,league_player_id)
,UNIQUE (league_player_id)
);
INSERT INTO user_league_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
( league_player_id INT NOT NULL
, starting_price INT NOT NULL DEFAULT '0'
, PRIMARY KEY (league_player_id)
);
INSERT INTO transfer_list VALUES (2,84),(3,120),(5,6),(7,5);
SELECT lp.*
, ulp.league_player_id list1
, tl.league_player_id list2
, lp.league_id = 101
FROM league_player lp
LEFT
JOIN user_league_player ulp
ON ulp.league_player_id = lp.league_player_id
LEFT
JOIN transfer_list tl
ON tl.league_player_id = lp.league_player_id;
+------------------+-----------+-----------+-----------------+-------+-------+-------+--------------------+
| league_player_id | league_id | player_id | player_name | level | list1 | list2 | lp.league_id = 101 |
+------------------+-----------+-----------+-----------------+-------+-------+-------+--------------------+
| 1 | 101 | 1 | Anthony Modeste | 2 | 1 | NULL | 1 |
| 2 | 101 | 2 | Gary Cahill | 1 | 2 | 2 | 1 |
| 3 | 101 | 3 | Papiss Cisse | 1 | 3 | 3 | 1 |
| 4 | 101 | 4 | Josh McEachran | 2 | 4 | NULL | 1 |
| 5 | 102 | 5 | Darron Gibson | 3 | 5 | 5 | 0 |
| 6 | 101 | 6 | Robbie Keane | 2 | 6 | NULL | 1 |
| 7 | 102 | 7 | Robbie Keane | 2 | 7 | 7 | 0 |
| 8 | 102 | 8 | Scott Allan | 3 | 8 | NULL | 0 |
| 9 | 102 | 9 | Paul Scholes | 2 | NULL | NULL | 0 |
| 10 | 101 | 10 | Thierry Henry | 1 | NULL | NULL | 1 |
| 11 | 102 | 11 | Thierry Henry | 1 | NULL | NULL | 0 |
| 12 | 101 | 12 | Landon Donovan | 2 | NULL | NULL | 1 |
+------------------+-----------+-----------+-----------------+-------+-------+-------+--------------------+
When your Champions League or FA Cup gets going, things will become VERY confusing!