Query returning wrong values

[nick2price]

Hi
I have my tables in the following relationships

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

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

 

[redneckgeek]

First off, you need to join the competitor and results tables in your query.
Otherwise, you'll get a cartestian result set. That's why it seems to be showing each athlete with the same time.

 

[nick2price]

Sorry, i am really bad at sql, this is to integrate in my java program. Would a join on these two just consist of

r.competitor_id = c.competitor_id

If so, where abouts would i fit this into my query?

 

[redneckgeek]

Sorry, i am really bad at sql, this is to integrate in my java program. Would a join on these two just consist of

r.competitor_id = c.competitor_id

If so, where abouts would i fit this into my query?

Change your FROM statement to

FROM tblResulsts r inner join tblCompetitors c on r.competitor_id=c.competitor_id

 

[nick2price]

The problem with that however is that it then expects competitor id as an extra parameter, where i only want it to return the 3 fastest times dependant on the event name and round number. Not sure how to do it so it only needs them two parameters.

 

[redneckgeek]

Now that I can see your diagram (couldn't see it at the office for some reason), it becomes a bit easier. I was wondering why your WHERE looked that way. You were being asked for parameter on competitor id because your tblCompetitor does not have a competitor_id field.

Try something like this:

SELECT TOP 3 r.result, c.Nationality, c.First_Name, c.Last_Name
FROM tblResults r inner join tblCompetitor c on r.competitor_id=c.ID
inner join tblEvent e on r.event_id=e.ID 
inner join tblRound ro on r.round_id=ro.ID
WHERE e.Event_Name = '100M Run' and  
       ro.Round_Number = 'Round_1'
ORDER BY r.result

 

[nick2price]

I see what your getting at with the inner joins. When i try and run the query as you have it, i am being returned with
syntax error(missing operator)in query expression 'r.competitor_id=c.ID
inner join.....
inner join.....

What would be causing this error?

 

[jal]

I see what your getting at with the inner joins. When i try and run the query as you have it, i am being returned with
syntax error(missing operator)in query expression 'r.competitor_id=c.ID
inner join.....
inner join.....

What would be causing this error?

In access you need parentheses when doing multiple inner joins. Here's the rule. End each each INNER JOIN line with a closing parentheses (a right-hand paran). And then cluster all the left-hand parenths after the word FROM


SELECT TOP 3 r.result, c.Nationality, c.First_Name, c.Last_Name
FROM (((tblResults as r
inner join tblCompetitor as c on r.competitor_id=c.ID)
inner join tblEvent e on r.event_id=e.ID)
inner join tblRound ro on r.round_id=ro.ID)
WHERE e.Event_Name = '100M Run' and
ro.Round_Number = 'Round_1'
ORDER BY r.result

But once you save the query, Access rearranges the parenths !! Oh well, whatever.