Queries and sub-queries

[RoryGren]

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

[Suomedia]

If your script works but takes forever, you probably need to add indexes to your tables.

Matti Ressler
Suomedia

[RoryGren]

Okay, thanks. I'll have a bash at that.

[blater]

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

[RoryGren]

You are a star! My query took about 14 secs, this one takes 0.2 secs!

Thank you very much!

[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.

[blater]

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 :icon_smile: ....
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

[RoryGren]

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!

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.ATHLETE

Thanks for all the help!