mysql - 获取没有相关记录MySQL的记录列表

Question

我想知道“TX”中哪支球队没有打过比赛。 (换句话说，我正在寻找在许多表中没有相关记录的记录选择。)

这是 SQL:

(或者，如果您更喜欢 sql fiddle，请访问:http://sqlfiddle.com/#!2/14106)

CREATE  TABLE `Team` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `Name` VARCHAR(45) NULL ,
  `State` VARCHAR(45) NULL ,
  PRIMARY KEY (`ID`) );

CREATE  TABLE `Games` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `Team_ID` INT NULL ,
  `Game_Day` DATE NULL ,
  PRIMARY KEY (`ID`) );

    INSERT INTO `Team` (`Name`, `State`) VALUES ('Rams', 'TX');
    INSERT INTO `Team` (`Name`, `State`) VALUES ('Rockets', 'OK');
    INSERT INTO `Team` (`Name`, `State`) VALUES ('Bombers', 'TX');
    INSERT INTO `Team` (`Name`, `State`) VALUES ('Yellow Jackets', 'NV');
    INSERT INTO `Team` (`Name`, `State`) VALUES ('Wildcats', 'CT');
    INSERT INTO `Team` (`Name`, `State`) VALUES ('Miners', 'CO');
    INSERT INTO `Team` (`Name`, `State`) VALUES ('Bolts', 'TX');

    INSERT INTO `Games` (`Team_ID`, `Game_Day`) VALUES ('2', '2013-03-16');
    INSERT INTO `Games` (`Team_ID`, `Game_Day`) VALUES ('2', '2013-01-01');
    INSERT INTO `Games` (`Team_ID`, `Game_Day`) VALUES ('3', '2013-04-16');
    INSERT INTO `Games` (`Team_ID`, `Game_Day`) VALUES ('5', '2013-02-02');
    INSERT INTO `Games` (`Team_ID`, `Game_Day`) VALUES ('4', '2013-02-12');
    INSERT INTO `Games` (`Team_ID`, `Game_Day`) VALUES ('6', '2013-01-09');
    INSERT INTO `Games` (`Team_ID`, `Game_Day`) VALUES ('6', '2013-01-01');
    INSERT INTO `Games` (`Team_ID`, `Game_Day`) VALUES ('3', '2013-05-01');

我应该得到结果:

ID     Name    
1      Rams
7      Bolts

Answer 1

SELECT `ID`, `Name` FROM `TEAM` 
  WHERE `ID` NOT IN (SELECT DISTINCT(`Team_ID`) from `Games`) 
  AND `State` = 'TX'; 

SqlFiddle here .