Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2007
    Posts
    8

    Unanswered: help with select query

    SELECT firstName, lastName, position, GamePlayed.gameID
    FROM Player, Team, GamePlayed, Game, Tournament
    WHERE Tournament.name = 'NCAA Division I'
    AND Tournament.year = '2007'
    AND Tournament.tourneyID = Game.tourneyID
    AND GamePlayed.teamID = Team.teamID
    AND GamePlayed.gameID = Game.gameID
    AND Team.teamID = Player.teamID
    AND Game.round = '6'
    AND Player.firstYear <= '2007'
    AND Player.lastYear >= '2007'
    AND GamePlayed.points > ANY(

    SELECT Points
    FROM GamePlayed GP
    WHERE GamePlayed.gameID = GP.gameID
    )

    seems it doesn't like this b/c the subquery doesn't know what GamePlayed.gameID is

    whats the right way to do this?

    thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the right way to do what?

    in words, what are you trying to get?

    the teams that didn't score the lowest?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    8
    trying to get the team that won the game, specifically the championship game (round 6).

    THere are two gameplayed records for the two teams, for each game.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so as i understand your query, the points of the game played have to be greater than the points of any other game? how does this get you the team that won the game?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Posts
    8
    there are two gameplayed records associated with each game. Say team A gameplayed game 1, and scored 80 points. team B also gameplayed game 1, and scored 70 points. THe query should return the players on team A

  6. #6
    Join Date
    Mar 2007
    Posts
    8
    I figured it out, I'm using mySQL, it doesn't support f-ing subqueries, thats such bull, I'm never using mySQL again

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mysql does support subqueries, has done for several releases

    are you still on an ancient release? and if so, why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Posts
    8
    MySQL 3.23.54 i think

    its not my server, its my school account.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I haven't looked at thes closely, but maybe you want to use this predicate (note the '>='):
    Code:
    GamePlayed.points >= ANY(
       SELECT Points
       FROM GamePlayed GP
       WHERE GamePlayed.gameID = GP.gameID )
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Mar 2004
    Posts
    480
    so your school is using a database that is two major releases behind. Are they also runninng Windows 98?

  11. #11
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Indeed it sounds like your school are being rather backward with what they provide. Nevertheless I'm sure there is an easy to way to do it with a join (rudy?).

    I have done one which relies on a ORDER BY clause and then LIMITs the resultset to 1 (being the topmost result). A !nasty! hack.

    Code:
    SELECT firstName, lastName, position, GamePlayed.gameID
    FROM Player, Team, GamePlayed, Game, Tournament
    WHERE Tournament.name = 'NCAA Division I'
    AND Tournament.year = '2007'
    AND Tournament.tourneyID = Game.tourneyID
    AND GamePlayed.teamID = Team.teamID
    AND GamePlayed.gameID = Game.gameID
    AND Team.teamID = Player.teamID
    AND Game.round = '6'
    AND Player.firstYear <= '2007'
    AND Player.lastYear >= '2007'
    ORDER BY GamePlayed.points DESC
    LIMIT 0,1

  12. #12
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    As no table layout has been given I have taken a guess and supplied some input data for those looking to try this example out :

    Code:
    # Table `Player`
    CREATE TABLE Player(
      firstName VARCHAR(20) NOT NULL,
      lastName VARCHAR(20) NOT NULL,
      firstYear INT(4) UNSIGNED NOT NULL,
      lastYear INT(4) UNSIGNED NOT NULL,
      teamID INT UNSIGNED NOT NULL
    ) ENGINE=InnoDB;
    
    # Table `Game`
    CREATE TABLE Game(
      gameID INT UNSIGNED NOT NULL,
      `round` INT UNSIGNED NOT NULL,
      tourneyID INT UNSIGNED NOT NULL
    ) ENGINE=InnoDB;
    
    # Table `Tournament`
    CREATE TABLE Tournament(
      tourneyID INT UNSIGNED NOT NULL,
      name VARCHAR(20) NOT NULL,
      `year` INT(4) UNSIGNED NOT NULL
    ) ENGINE=InnoDB;
    
    # Table `GamePlayed`
    CREATE TABLE GamePlayed(
      gameID INT UNSIGNED NOT NULL,
      teamID INT UNSIGNED NOT NULL,
      points INT UNSIGNED NOT NULL
    ) ENGINE=InnoDB;
    
    # Table `Team`
    CREATE TABLE Team(
      teamID INT UNSIGNED NOT NULL,
      name VARCHAR(20)
    ) ENGINE=InnoDB;
    
    # Insert Player values.
    INSERT INTO
    Player(firstName,lastName,firstYear,lastYear,teamID)
    VALUES
    ('Frank','Belini','2006','2007',1),
    ('Garry','Mocha','2006','2007',2),
    ('Fred','Astair','2006','2007',3);
    
    # Insert Team values.
    INSERT INTO team(teamID,name)
    VALUES(1,'Conjugulars'),
    (2,'Fatsos'),
    (3,'White Lightning');
    
    # Insert Tournament
    INSERT INTO tournament(tourneyID,name,`year`)
    VALUES(1,'First Tournament',2006);
    
    # Insert two sorts of games for this tournament
    INSERT INTO
    game(gameID,`round`,tourneyID)
    VALUES
    (1,1,1),
    (1,2,1),
    (1,3,1),
    (2,1,1),
    (2,2,1),
    (2,3,1);
    
    # Insert what games have been played
    INSERT INTO
    GamePlayed(gameID,teamID,points)
    VALUES
    (1,1,20),
    (1,2,47),
    (1,3,52),
    (2,1,82),
    (2,2,17),
    (2,3,111);

  13. #13
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Further to my previous reply I have included a partial SQL statement for those wishing to see what is returned :

    Code:
    SELECT firstName, lastName, points, GamePlayed.gameID
    FROM Player, Team, GamePlayed, Game, Tournament
    WHERE Tournament.name = 'First Tournament'
    AND Tournament.year = '2006'
    AND Tournament.tourneyID = Game.tourneyID
    AND GamePlayed.teamID = Team.teamID
    AND GamePlayed.gameID = Game.gameID
    AND Team.teamID = Player.teamID
    AND Game.round = '3'
    AND Player.firstYear <= '2006'
    AND Player.lastYear >= '2006'
    ORDER BY GamePlayed.points DESC
    Notice in the above that I have left out position (as I have in my table layout) because I don't actually know where this field comes from and what part it plays in the resultset.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i'm not even gonna try to understand what's going on here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    LOL, i think we're all confused

Posting Permissions

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