Q:

Mysql use Join as alias on second join

I need to compare the value from the first Join to the second..
SELECT d.* FROM nt_schedule d 
JOIN (SELECT DISTINCT schedule_id, locality, key_position 
        FROM nt_schedule_stops 
        WHERE locality = 'Greece') AS getit 
    USING (schedule_id)
JOIN (SELECT DISTINCT schedule_id, locality, key_position 
        FROM nt_schedule_stops 
        WHERE locality = 'London' AND !!key_position>getit.key_position!!` `) AS sendit 
    USING (schedule_id)
GROUP BY schedule_id`

Is this possible?

A:

SELECT d.* 
FROM nt_schedule d 
JOIN (
  SELECT DISTINCT schedule_id, locality, key_position 
  FROM nt_schedule_stops 
  WHERE locality = 'Greece') AS getit 
ON getit.schedule_id = d.schedule_id
JOIN (
  SELECT DISTINCT schedule_id, locality, key_position 
  FROM nt_schedule_stops 
  WHERE locality = 'London' 
  ) AS sendit 
ON sendit.schedule_id = d.schedule_id
  AND sendit.key_position > getit.key_position
GROUP BY schedule_id

