Forum Moderators: open
select rider_key from rider as Ri where not exists
(select * from course as C where C.club_bar = 1 and not exists
(select * from result as R, event as E
where C.course_key = E.course
and R.event = E.event_key
and R.rider = Ri.rider_key))
and the following the average speed of the best rides on qualifying courses for a given rider (in this case rider 2):
select avg(M) from (select course_key,
(select max(av_speed) from event, result where rider = 2 and event.course = course_key and result.event=event_key and event.incinpb = 1) as M
from course where club_bar = 1) as N
What I would like to be able to do is marry the 2 together so that the final result is a list of riders qualifying (as of 1st query), with their average speed (as of 2nd query) - ordered by average speed, but I can't get my head around how to do it.
Any help would be much appreciated
select rider_key, (select max(av_speed)
from event, result
where rider = rider.rider
and event.course = course_key
and result.event=event_key and event.incinpb = 1)
from rider as Ri
where not exists
(select * from course as C where C.club_bar = 1 and not exists
(select * from result as R, event as E
where C.course_key = E.course
and R.event = E.event_key
and R.rider = Ri.rider_key))
The trick is to correlate rider in the outer query to rider in the embedded query.
where rider = Ri.rider
select rider_key, (select avg(M) from (select course_key,
(select max(av_speed) from event, result where rider = Ri.rider_key and event.course = course_key and result.event=event_key and event.incinpb = 1) as M
from course where club_bar = 1) as N)
from rider as Ri
where not exists
(select * from course as C where C.club_bar = 1 and not exists
(select * from result as R, event as E
where C.course_key = E.course
and R.event = E.event_key
and R.rider = Ri.rider_key))
but it doesn't work, coming up with the error
#1054 - Unknown column 'Ri.rider_key' in 'where clause'
(select avg(M)
from (select course_key, (select max(av_speed)
from event JOIN result ON result.event=event.event_key
where result.rider = Ri.rider_key
and event.course = course_key
and event.incinpb = 1) as M
from course
where club_bar = 1) as N)
with
rider = 2
then it works (but obviously all rows show rider 2's average)
Is this a question of scope? The query containing rider = Ri.rider_key cannot 'see' the rider table of the second query?
