...

View Full Version : Comparing two table to find what doesnt exist



alex57
03-03-2007, 07:30 PM
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
03-03-2007, 09:24 PM
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!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum