CODEWITHSUNDEEP
mysql
JVMX
JVMX

Reputation: 1078

Getting List of records with No related records MySQL

I wish to know which team in "TX" have not played a game. (In other words Im looking for a selection of records where there is no related record in the many table.)

Here is the SQL:

(Or if You prefer the sql fiddle is here: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');

I should get the result:

ID     Name    
1      Rams
7      Bolts

Upvotes: 5

Views: 2409

Answers (4)

vidit
vidit

Reputation: 6461

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

SqlFiddle here.

Upvotes: 6

Bohemian
Bohemian

Reputation: 425378

Use an outer join, selecting only those rows that don't match

SELECT t.*
FROM TEAM t
LEFT JOIN GAMES g ON g.team_id = t.id
WHERE t.state = 'TX'
AND g.team_id is null -- return only rows that *don't* join

This this running in SQL Fiddle

Note that using a join will out-perform a sub-query approach, especially when data sets become large.

Upvotes: 6

Ed Gibbs
Ed Gibbs

Reputation: 26363

You can also left-join to the Games table and filter for where there isn't a corresponding Games row. This is usually faster than NOT IN when the tables have a lot of rows:

SELECT Team.ID, Team.Name
FROM Team
LEFT JOIN Games ON Team.ID = Games.Team_ID
WHERE Team.State = 'TX' AND Games.ID IS NULL;

If there isn't a Games row to go with the Teams row, the Games.ID column will be null in the result, so if you filter on Games.ID IS NULL you'll get all the rows where a team has no games.

There's a SQL Fiddle here.

Upvotes: 4

Lahiru Rajeew Ananda
Lahiru Rajeew Ananda

Reputation: 310

Hope this would help.

select t.ID,t.NAME
FROM Team t
WHERE t.state = 'TX'
AND t.id NOT IN (SELECT g.TEAM_ID FROM Games g)

Upvotes: 1

Related Questions