Sub SELECTs & MYSQL

[admin]

Hi (i am giving more info in this post)

I have 2 queries.........

SELECT ROUTE_ID,COUNT(ROUTE_ID) AS numstops
FROM stop_visit_order,stop
WHERE stop_visit_order.STOP_ID=stop.STOP_ID
AND (STOP_DESC='VAR1') OR STOP_DESC='VAR2')
GROUP BY roue_ID
HAVING numstops 2;

I need to make the above part of a sub select query for the query below.......

SELECT ROUTE_ID,ARRIVE_TIME
FROM stop_visit_time,stop
WHERE stop_visit_time.STOP_ID=stop.STOP_ID
AND STOP_DESC='VAR1'
AND stop_visit_time.ARRIVE_TIME>=#VAR3# AND ROUTE_ID IN (results from 1st query);

To become.......

SELECT ROUTE_ID,ARRIVE_TIME
FROM stop_visit_time,stop
WHERE stop_visit_time.STOP_ID=stop.STOP_ID
AND STOP_DESC='Acklam, The Coronation'
AND stop_visit_time.ARRIVE_TIME>=#05:00:00#
AND ROUTE_ID IN
(SELECT ROUTE_ID,COUNT(ROUTE_ID) AS numstops
FROM stop_visit_order,stop
WHERE stop_visit_order.STOP_ID=stop.STOP_ID
AND (STOP_DESC='Acklam, The Coronation') OR STOP_DESC='Linthorpe Village')
GROUP BY ROUTE_ID
HAVING numstops = 2;

I can get this working in MSACCESS(which i used as a prototype) but MYSQL cannot handle the sub select query, is there any way of getting round this with MYSQL?

Thanks.