mySQL 4.1.22 - JOIN problem

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

[I]DOES NOT PRODUCE ....[/I]
+----------+-------+--------------+
| playerID | name  | playerClubID |
+----------+-------+--------------+
| 1        | Smith | 100          |
| 3        | Jones | 100          |
+----------+-------+--------------+

Code (markup):

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

 

What you want is:

SELECT * 
FROM Players LEFT OUTER JOIN 
Squads ON Players.PlayerID = Squads.sqplayerID 
WHERE Players.PlayerClubID = 100 AND Squads.sqplayerID is null;

Code (SQL):

 

\o/

yay!

That works perfectly!

Thank you SO much!