Re: problem selecting from one table where not in another

[laptop alias]

1. It's just shorthand for 'if lp.league_id = 101 then true (1) else false (0)'.

2. As long as the relevant column is found in the first table (of a LEFT [OUTER] JOIN) then the condition can go in the WHERE clause. For example:

(but first let's tidy up some of those player_ids...)

UPDATE league_player SET player_id = 6 WHERE player_name = 'Robbie Keane';
UPDATE league_player SET player_id = 7 WHERE player_name = 'Scott Allan';
UPDATE league_player SET player_id = 8 WHERE player_name = 'Paul Scholes';
UPDATE league_player SET player_id = 9 WHERE player_name = 'Thierry Henry';
UPDATE league_player SET player_id = 10 WHERE player_name = 'Landon Donovan';

SELECT lp.*
  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
 WHERE lp.league_id = 101
   AND ulp.league_player_id IS NULL
   AND tl.league_player_id IS NULL;
   
+------------------+-----------+-----------+----------------+-------+
| league_player_id | league_id | player_id | player_name    | level |
+------------------+-----------+-----------+----------------+-------+
|               10 |       101 |         9 | Thierry Henry  |     1 |
|               12 |       101 |        10 | Landon Donovan |     2 |
+------------------+-----------+-----------+----------------+-------+