Query help need please

[nick2price]

I have a database with the relationships as so:

I am trying to create a query which will return the top 3 fastest times for the 100M Run for Round 1. This is what i have attempted, please egnore the syntax and everything as it is written for java.

  String SELECT_TOP_TIMES = 
   "select r.result " +
      "from tblResults as r " +
      "where r.Event_ID = " +
            "(select e.ID " +
            "from tblEvent as e " +
            "where e.Event_Name = ?) " +
      "where r.Round_ID = " +
        "(select ro.ID " +
            "from tblRound as ro " +
            "where ro.Round_Number = ?) " +
            "order by r.result ";

Please can you advise me how to get it correct,
cheers

 

[LPurvis]

Presumably you're wanting this for a specific Event and Round only (not aggregated for all) and you seem to be wanting to specify them by name rather than ID (hence the involvement of the related tables)?

SELECT TOP 3 r.result 
FROM 
 (tblResults as r 
    INNER JOIN 
  tblEvent as e
    ON r.Event_ID = e.ID
 )
   INNER JOIN
 tblRound ro
   ON r.Round_ID = ro.ID
WHERE
  e.Event_Name = [EnterEvent]
    AND
  ro.Round_Number = [EnterRound]
ORDER BY r.result DESC

Your parameters are now named parameters - as that's what Jet supports.

 

[nick2price]

Are my table relationships ok as seen in the picture in my original post? The reason why i ask this is that the query returns three results, but they are not the three fastest.

 

[LPurvis]

Actually since you're testing times you don't want the Descending (DESC) order. Force of habit saw me type it ;-)
The query should end
ORDER BY r.result

 

[nick2price]

Even when i have it without DESC, it seems to return any three times. This is the reason i think my relationships might be wrong.

 

[LPurvis]

I don't see why the relationships would be a problem - this is ultimately all about the tblResults table.
Change your first (SELECT) line to
SELECT TOP 3 r.result, e.Event_Name, ro.Round_Number
to assure yourself that the correct data is being returned (that should be your relationship concerns alleviated).

Fundamentally - what data type is the result field in tblResults?

 

[nick2price]

Lol, your last line solved it. The result field was set to text but as soon as i changed it to number, it worked. Cheers for the help.

 

[LPurvis]

Thought so. ;-)
No worries.