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.
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 areMeet(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 cINNER JOIN Round rON r.CompetitorID=c.CompetitorIDINNER JOIN Event eON e.eventID=r.eventIDWHERE r.roundNumber = 1 AND e.eventName = '100M run' ORDER BY c.Time_Set ASC[/code] |
|
|
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:MEETMeet_ID(pk)Meet_NameEVENTEvent_ID(pk)Meet_ID(fk)Event_NameROUNDRound_ID(pk)Event_ID(fk) Round_Number ROUNDCOMPETITOR RoundCompetitor_ID(pk) Round_ID(fk)Competitor_ID(fk) COMPETITOR Competitor_ID(pk)Competitor_NameTime_SetNow 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? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-24 : 01:28:05
|
Then post in Access forum please |
|
|
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 slightlyMeet(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_1I am trying to do this without Inner Joins, so i have tried.SELECT [Competitor].[Time_Set]FROM CompetitorWHERE (((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? |
|
|
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 slightlyMeet(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_1I am trying to do this without Inner Joins, so i have tried.SELECT [Competitor].[Time_Set]FROM CompetitorWHERE (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. |
|
|
|
|
|
|
|