Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Sep 2006
    Posts
    216
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Comparing two table to find what doesnt exist

    I have two tables:

    Code:
    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

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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:

    Code:
    select 
    footballers.footballerID
    from footballers
    where footballers.footballerID 
    NOT IN
    (select footballerscore.footballerid
    from footballerscore
    where gameweekid = x)
    GO READING FC!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •