Comparing two table to find what doesnt exist

[alex57]

I have two tables:

CREATE TABLE `FootballerScore` (
  `footballerID` mediumint(4) NOT NULL default '0',
  `GameWeekID` mediumint(4) NOT NULL default '0',
  `GameRuleID` mediumint(4) NOT NULL default '0',
  `Goals` mediumint(4) NOT NULL default '0',
  `Assists` mediumint(4) NOT NULL default '0',
  `CleanSheets` mediumint(4) NOT NULL default '0',
  `YellCards` mediumint(4) NOT NULL default '0',
  `RedCards` mediumint(4) NOT NULL default '0',
  `WeeklyScore` mediumint(4) NOT NULL default '0',
  `AccScore` mediumint(6) NOT NULL default '0',
  PRIMARY KEY  (`footballerID`,`GameWeekID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `footballers` (
  `footballerID` mediumint(15) NOT NULL default '0',
  `fname` varchar(15) collate latin1_general_ci NOT NULL default '',
  `sname` varchar(15) collate latin1_general_ci NOT NULL default '',
  `value` decimal(15,1) NOT NULL default '0.0',
  `PremTeamID` mediumint(15) NOT NULL default '0',
  `position` varchar(15) collate latin1_general_ci NOT NULL default '',
  `Importance` varchar(4) collate latin1_general_ci NOT NULL default '',
  PRIMARY KEY  (`footballerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

The footballers table basically has a list of all the players in the premiership and the footballerscore table has a list of the player who played in a given week. For any one week I need to find out who DIDNT play. I can do this by comparing all the footballerIDs in both tables and the difference is the group I need. However I'm not sure how to put this into an SQL statement . Any ideas??

thanks in advance

[guelphdad]

If all the footballers who play this week show up in the footballerscore table, and the week can be designated as x for perhaps 13 or week 13 or '2007-03-03' then you can get those not playing from:

select 
footballers.footballerID
from footballers
where footballers.footballerID 
NOT IN
(select footballerscore.footballerid
from footballerscore
where gameweekid = x)

GO READING FC!