|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-06-24 : 12:28:10
|
| John writes "These queries have been driving me nuts for years, I got an assist on one of them but the end result was still not correct. I am not a SQL guru and so here is the problems.1) Given an Age Group, Sex, Distance, Stroke, course for the last three or n months show the top n times (an athlete can not be shown more than once), displaying their name, time(result), date of time, name of meet, result ID.2) For an athlete show their best time in each event including date, time(result), name of meet, result ID.3) Given an event (distance, stroke, course), a time, age date, age group and sex display all athletes with a time faster than the input time (again an athlete must be listed only once) display the time, the date of time, meet name, result ID.The three tables are swimmer, results, and meets. The table swimmer has athleteID, Firstname, Lastname, Date of Birth, Sex.The table Results has ResultID, Coursecode, distance, stroke, MeetID, EventAge, swimtime, LSCcode, ClubCode. The Meet table has MeetID, Meetname, Startdate, Enddate.The startdate is important to the third query to determing age as of specified date, otherwise it is displayed in all results. THe ResultID in the result table is important as it is the key for finding splits for that particular swim. The assist I got used a join on itself. I still have no clue how it works but it gave all the right answers except the resultID for the first two.Example for query 1SELECT R.EventAge, S.Sex, R.CourseCode, R.Stroke, R.DistanceCode, S.Lastname, S.Firstname, MIN(R.SwimTime) AS besttime, MIN(DISTINCT S.SwimmerID) AS AthID, MIN(DISTINCT R.TeamCode) AS Club, MIN(DISTINCT R.ResultID) AS ResultIDFROM dbo.tblSwimmers S INNER JOIN dbo.tblResults R ON S.SwimmerID = R.SwimmerID INNER JOIN dbo.tblMeets M ON R.MeetID = M.MeetIDWHERE (M.StartDate > DATEADD(month, - 3, GETDATE())) AND (R.SwimTime <> 0)GROUP BY R.EventAge, S.Sex, R.CourseCode, R.Stroke, R.DistanceCode, S.Lastname, S.FirstnameAny help would be appreciated, we are Colorado Swimming a Local Swimming Committeee of USA Swimming, and as far as we are aware the only member organization with a SQL Server database that contains times that go back to 1996 and have electronic archives going back to 1990 which we are loading. There is a lot more complex things we would like to do with this database, just no expertise.John Lorimer" |
|
Athlete Times Query problem with three tables