mySQL 4.1.22 - JOIN problem

[gfreeman]

I have two tables, players and squads

 

All players are in the players table, but only some of them are in the squads table. I am trying to produce a list of players that are on the same club, but not in the squads table

 

mysql> SELECT * FROM players
+----------+--------+--------------+
| playerID | name   | playerClubID |
+----------+--------+--------------+
| 1        | Smith  | 100          |
| 2        | Brown  | 100          |
| 3        | Jones  | 100          |
| 4        | Black  | 101          |
| 5        | White  | 101          |
+----------+--------+--------------+

mysql> SELECT * FROM squads
+------------+-------+----------+
| sqPlayerID | name  | sqClubID |
+------------+-------+----------+
| 2          | Brown | 100      |
| 4          | Black | 101      |
+------------+-------+----------+

mysql> SELECT * FROM players INNER JOIN squads 
ON players.playerClubID = squads.sqClubID AND 
players.playerClubID = 100 AND 
players.playerID <> squads.squadID ORDER BY 
players.playerID

DOES NOT PRODUCE MY DESIRED OUTPUT....
+----------+-------+--------------+
| playerID | name  | playerClubID |
+----------+-------+--------------+
| 1        | Smith | 100          |
| 3        | Jones | 100          |
+----------+-------+--------------+

 

What mySQL command would produce a list of entries from the players table with a specific playerClubID who are not in the squad table?

[gfreeman]

And the answer is ...

 

SELECT * FROM Players LEFT OUTER JOIN squads ON players.playerID = squads.sqPlayerID WHERE players.playerClubID = 100 AND squads.sqPlayerID IS NULL;