Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Posts
    9

    Unanswered: Subject: Distinct not distinct...

    I have this query...
    Code:
    select distinct games.*, teams.teamname as teamname 
      from games, teams 
    where 
      (((games.homeid=teams.id) or (games.visitorid=teams.id)) 
    and (teams.division="A") 
    and ((games.homescore is not null) and (games.visitorscore is not null)) 
    and (games.homescore<>999 and games.visitorscore<> 999)) 
    order by games.homeid, games.visitorid
    But when two teams in the same division ("A") play each other,
    i get two results for the one game.

    How can I get only the one entry for the game played between two
    division opponents?

    {Note: games with 999 as score are rainouts}

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Distinct excludes duplicate rows, not duplicate values for each row.
    If you exclude the teamname from your select it should only return unique games.

  3. #3
    Join Date
    Apr 2002
    Posts
    9

    Solution

    Here is the solution:

    $sql= 'SELECT g.* FROM games g, teams t1, teams t2 WHERE
    (g.homeid=t1.id AND g.visitorid=t2.id) AND (t1.division="A"
    or t2.division="A")';
    $sql = $sql.' AND (g.homescore IS NOT NULL AND g.visitorscore IS
    NOT NULL) AND (g.homescore<>999 AND
    g.visitorscore<>999) ORDER BY g.gamenum';
    $query = mysql_query($sql);

Posting Permissions

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