•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 361,942 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,678 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser:
Views: 612 | Replies: 7 | Solved
 |
•
•
Join Date: Oct 2007
Location: South Africa
Posts: 59
Reputation: 
Rep Power: 1
Solved Threads: 6
Junior Poster in Training
Hi all.
I have posted on this before with no response. Let me try a different way.
I have a db with a few tables :-
ATHLETE, which contains fields ATHLETE (the record Id), FIRST, LAST, AGE and SEX;
MEET, with fields MEET (the record Id), and MNAME;
RESULT, with fields ATHLETE, MEET, STROKE, DISTANCE, and SCORE.
The RESULT table obviously has multiple entries per athlete/swimmer - they can swim the 50 Freestyle at multiple meets.
I want to do the following:
On the page, a swimmer is selected from a list. The ATHLETE id selected and passed as a variable. The stroke and distance is then selected and also passed as a variable.
The script must then find that swimmer's age and sex from ATHLETE and then pull out only the top time for each athlete in that age group and sex, for that stroke and distance and then rank them.
I have a script already, but it takes a long time to return a result set.
In anticipation ... ;-)
Thanks
I have posted on this before with no response. Let me try a different way.
I have a db with a few tables :-
ATHLETE, which contains fields ATHLETE (the record Id), FIRST, LAST, AGE and SEX;
MEET, with fields MEET (the record Id), and MNAME;
RESULT, with fields ATHLETE, MEET, STROKE, DISTANCE, and SCORE.
The RESULT table obviously has multiple entries per athlete/swimmer - they can swim the 50 Freestyle at multiple meets.
I want to do the following:
On the page, a swimmer is selected from a list. The ATHLETE id selected and passed as a variable. The stroke and distance is then selected and also passed as a variable.
The script must then find that swimmer's age and sex from ATHLETE and then pull out only the top time for each athlete in that age group and sex, for that stroke and distance and then rank them.
I have a script already, but it takes a long time to return a result set.
In anticipation ... ;-)
Thanks
•
•
Join Date: Mar 2008
Posts: 153
Reputation:
Rep Power: 1
Solved Threads: 19
Junior Poster
If your script works but takes forever, you probably need to add indexes to your tables.
Matti Ressler
Suomedia
Matti Ressler
Suomedia
If you want your dreams to come true, the first thing you must do is to wake up....
Suomedia - Dynamic Content Management
Suomedia - Dynamic Content Management
•
•
Join Date: Oct 2007
Location: South Africa
Posts: 59
Reputation:
Rep Power: 1
Solved Threads: 6
Junior Poster in Training
Okay, thanks. I'll have a bash at that.
•
•
Join Date: Mar 2008
Posts: 25
Reputation:
Rep Power: 1
Solved Threads: 7
Light Poster
The query below should do what you want; It will always be a fairly chunky query and won't be lightening fast, no getting around that for the information you want, but should run reasonably.
select b.first, b.last,
min(score) as bestTime -- assuming that the lowest score is the best one
from athlete a,
athlete b,
result r
where a.athlete = $athlete -- find our athlete
-- This join is where all the database work is...
and b.age = a.age -- find all the others of the
and b.sex = a.sex -- same age and sex.
and r.athlete = b.athlete -- find the results for the others
and r.stroke = $stroke -- in the given stroke
and r.distance = $distance -- and distance.
group by b.first, b.last
order by bestTime asc •
•
Join Date: Oct 2007
Location: South Africa
Posts: 59
Reputation:
Rep Power: 1
Solved Threads: 6
Junior Poster in Training
You are a star! My query took about 14 secs, this one takes 0.2 secs!
Thank you very much!
Thank you very much!
•
•
Join Date: Oct 2007
Location: South Africa
Posts: 59
Reputation:
Rep Power: 1
Solved Threads: 6
Junior Poster in Training
OK, Blater!
A twist to the tale!
I need to add the meet name - field MNAME, table MEET - for the BestTime, so that the swimmers can see which meet it was where they achieved their best time. Both tables - RESULT and MEET - have the MEET field.
A twist to the tale!
I need to add the meet name - field MNAME, table MEET - for the BestTime, so that the swimmers can see which meet it was where they achieved their best time. Both tables - RESULT and MEET - have the MEET field.
•
•
Join Date: Mar 2008
Posts: 25
Reputation:
Rep Power: 1
Solved Threads: 7
Light Poster
•
•
•
•
Originally Posted by RoryGren 
OK, Blater!
A twist to the tale!
I need to add the meet name - field MNAME, table MEET - for the BestTime, so that the swimmers can see which meet it was where they achieved their best time. Both tables - RESULT and MEET - have the MEET field.
As you've probably found out, the trick to this one is that if you just join MEET to the query and group by MNAME, you'll get a list of the best time for the athlete at every different meet they've attended.
There are several ways to write it to get the right result - I don't claim this is the best way, but it's the first that comes to mind
....You can use a sub-select to ensure you only print the meeting name for the meeting that they got their best time at e.g.
select b.first, b.last, m.mname, r.score
from athlete a,
athlete b,
result r,
meet m
where a.athlete = $athlete -- find our athlete
and b.age = a.age -- match them to all other athletes
and b.sex = a.sex -- in same age group and sex
and r.athlete = b.athlete -- join to get details of each matched athlete
and r.stroke = $stroke -- look at their results for the stroke
and r.distance = $distance -- and distance we are interested in
and r.score in ( -- match only to the best score
select min(score) -- for the athlete/stroke/distance
from result r2
where r2.athlete = r.athlete
and r2.stroke = r.stroke
and r2.distance = r.distance
)
and m.meet = r.meet -- now get the meet that the best score was done at
order by r.score •
•
Join Date: Oct 2007
Location: South Africa
Posts: 59
Reputation:
Rep Power: 1
Solved Threads: 6
Junior Poster in Training
Thanks, Blater!
That one seemed to get suck in a loop and I had to bomb it out.
I chatted to a friend who had been away on leave and he gave me this query which works like a bomb!
Thanks for all the help!
That one seemed to get suck in a loop and I had to bomb it out.
I chatted to a friend who had been away on leave and he gave me this query which works like a bomb!
select
a3.LAST, a3.FIRST, mt.MNAME, m2.MIN_SCORE
from ( select r2.ATHLETE, r2.MEET, m1.MIN_SCORE
from RESULT r2
join ( select a1.ATHLETE, min(SCORE) as MIN_SCORE
from RESULT r1
join ATHLETE a1
on (r1.ATHLETE = a1.ATHLETE)
Where r1.SCORE > 0
and r1.STROKE = $stroke
and r1.DISTANCE = $distance
and a1.AGE = $age
and a1.SEX = $gender
GROUP BY a1.ATHLETE
) as m1
on (r2.ATHLETE = m1.ATHLETE
and r2.SCORE = m1.MIN_SCORE)
) m2
join ATHLETE a3 on a3.ATHLETE = m2.ATHLETE
join MEET mt on mt.MEET = m2.MEET
order by m2.MIN_SCORE, a3.ATHLETEThanks for all the help!
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
Linear Mode
Similar Threads
- SQL Queries help (Database Design)
- Create Access Database using SQL queries (MS Access and FileMaker Pro)
- snmp queries to printer (Networking Hardware Configuration)
- Multiple queries within one php file (PHP)
- Updating database with related tables and queries?? (VB.NET)
- Log files for submitted LDAP queries (Windows NT / 2000 / XP / 2003)
- Using exisiting queries within MS Access (ASP.NET)
- need help with DISTINCT (MySQL)
- securit issues fundamental - few queries - follow up (Viruses, Spyware and other Nasties)
- request for ansi sql queries equalent to MS access (Visual Basic 4 / 5 / 6)
Other Threads in the MySQL Forum
- Previous Thread: display image from databse
- Next Thread: how to write nested queries in a function
