Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2008
    Posts
    16

    Unanswered: Query returns wrong values

    Hi
    I have my tables in the following relationships
    http://i35.tinypic.com/f2k292.jpg
    Now i am trying to perform a query which will return the 3 fastest times, with the associated Nationality, First_Name and Last_Name (of the athletes that got the time) for a given event and round.
    I have done the query as follows

    Code:
    SELECT TOP 3 r.result, c.Nationality, c.First_Name, c.Last_Name
    FROM tblResults AS r, tblCompetitor AS c
    WHERE r.Event_ID = 
                (select e.ID 
                from tblEvent as e 
                where e.Event_Name = '100M Run') 
          and r.Round_ID = 
          (select ro.ID 
                from tblRound as ro 
                where ro.Round_Number = 'Round_1')
    ORDER BY r.result;
    This however seems to return the fasest time for round 1, and that time is returned with every single athelete in my database (even from different rounds). I think i havnt stated in the query that i only want the associated athletes with the three fastest times. Any help would be greatly appreciated.
    cheers

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    As you do not join tblResults and tblCompetitor, the result is cartesian product. Add join condition to the WHERE query clause or use ANSI syntax (INNER JOIN) with the same condition.

    By the way, why did you post this question top Oracle forum? TOP 3 is not valid in Oracle world:
    Code:
    SQL> select top 3 dummy from dual;
    select top 3 dummy from dual
               *
    ERROR at line 1:
    ORA-00923: FROM keyword not found where expected

Posting Permissions

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