Posted by ZeldorBlat on 10/13/67 11:41

laredotornado@zipmail.com wrote:
> Hi, Using MySQL 4, is it possible (and how) would I write a single
> query to look at the following tables
>
> CRUISES
> CRUISE_ID CRUISE_NAME
> ---------------- ----------------------
> 1 Atlantic Cruise
> 2 Pacific Cruise
>
> CRUISE_STOPS
> CRUISE_ID DEPART_TIME DEPARTURE_NAME
> ----------------- ---------------------
> -------------------------------
> 1 4/1/2006 8:00 Miami
> 1 4/15/2006 9:00 Virginia Beach
> 2 8/1/2006 11:00 San Francisco
> 2 8/31/2006 8:00 Mexico
>
> and return the first depart ponit on the cruise. So the single query
> would return the following results
>
> CRUISE_ID CRUISE_NAME DEPARTURE_NAME
> ---------------- ------------------------
> -------------------------------
> 1 Atlantic Cruise Miami
> 2 Pacific cruise San Francisco
>
> Thanks for your help, - Dave

select c.cruise_id, c.cruise_name, cs.departure_name
from cruises c
join (select cruise_id, min(depart_time) depart_time
from cruise_stops
group by cruise_id) x
on c.cruise_id = x.cruise_id
join cruise_stops cs
on (c.cruise_id = cs.cruise_id
and x.depart_time = cs.depart_time)

[Back to original message]