Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Newbie stuck on sql query

Author  Topic 

nick2price
Starting Member

4 Posts

Posted - 2008-09-23 : 05:27:25
I have four tables all appropiately linked (I hope) with primary and foriegn keys. My tables are
Meet(meetID(PK), meetName)
Event(eventID(PK), meetID(FK), eventName)
Round(roundID(PK), eventID(FK),CompetitorID(FK), roundNumber)
Competitor(CompetitorID(PK), name, timeSet)

I have data entered into all these tables. What i need to do is create a query that will return the 3 fastest times (Time_Set), for Round 1 (roundNumber), of the 100M Run (eventName)

I am totally lost! I have tried stuff like
("SELECT Time_Set FROM Competitor WHERE roundNumber = 1 AND eventName = 100M run ORDER BY Time_Set ASC");

But its getting to the case where i am now guessing with no idea of what to do. Any help would be greatly appreciated.
cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 05:32:30
[code]SELECT TOP 3 c.Time_Set FROM Competitor c
INNER JOIN Round r
ON r.CompetitorID=c.CompetitorID
INNER JOIN Event e
ON e.eventID=r.eventID
WHERE r.roundNumber = 1 AND e.eventName = '100M run'
ORDER BY c.Time_Set ASC[/code]
Go to Top of Page

nick2price
Starting Member

4 Posts

Posted - 2008-09-23 : 15:54:34
I am using Microsot Access, is it not 1 INNER JOIN per SELECT? I think i might have a mistake in my tables. My scenario is this:
You'll have one or more meets,

A meet will have one or more events (e.g., 50 m freestyle, 4x100 freestyle relay, etc.).

An event will have one or more heats.

A heat will have zero or more individuals entered.

Each individual will have a time associated with their heat.

So i have designed my tables as so:
MEET
Meet_ID(pk)
Meet_Name

EVENT
Event_ID(pk)
Meet_ID(fk)
Event_Name

ROUND
Round_ID(pk)
Event_ID(fk)
Round_Number

ROUNDCOMPETITOR
RoundCompetitor_ID(pk)
Round_ID(fk)
Competitor_ID(fk)

COMPETITOR
Competitor_ID(pk)
Competitor_Name
Time_Set

Now i am trying to return the 3 fastest times (Time_Set), for Round 1 (Round_Number), for the 100M (Event_Name).
Are my tables set ok to perform this query?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 01:28:05
Then post in Access forum please
Go to Top of Page

nick2price
Starting Member

4 Posts

Posted - 2008-09-24 : 06:39:09
I have been going through tutorials, i am trying to do somthing different now. I have changed my tables slightly
Meet(meetID(PK), meetName)
Event(eventID(PK), meetID(FK), eventName)
Round(roundID(PK), eventID(FK), roundNumber)
Competitor(CompetitorID(PK), name, timeSet)
RoundCompetitor(roundID(fk), competitorID(fk)

I want to try and return all timeSet where roundNumber = Round_1
I am trying to do this without Inner Joins, so i have tried.
SELECT [Competitor].[Time_Set]
FROM Competitor
WHERE (((Competitor.Competitor_ID) IN (select Competitor_ID from RoundCompetitor)
AND (Round.Round_ID) IN (select Round_ID from RoundCompetitor where (Round_Number) = 'Round_1')));

Can u see where i am going wrong?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 07:32:49
quote:
Originally posted by nick2price

I have been going through tutorials, i am trying to do somthing different now. I have changed my tables slightly
Meet(meetID(PK), meetName)
Event(eventID(PK), meetID(FK), eventName)
Round(roundID(PK), eventID(FK), roundNumber)
Competitor(CompetitorID(PK), name, timeSet)
RoundCompetitor(roundID(fk), competitorID(fk)

I want to try and return all timeSet where roundNumber = Round_1
I am trying to do this without Inner Joins, so i have tried.
SELECT [Competitor].[Time_Set]
FROM Competitor
WHERE (
Competitor.Competitor_ID IN
(select Competitor_ID from RoundCompetitor AND Round.Round_ID IN
(select Round_ID from RoundCompetitor where Round_Number = 'Round_1')
)
);

Can u see where i am going wrong?


i think you've got table name wrong.modify as above and see.
Go to Top of Page
   

- Advertisement -