Queries and sub-queries

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

Matti Ressler
Suomedia

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

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

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

Thank you very much!

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.

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

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!