Tricky MySQL involving a join and a min

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

Re: Tricky MySQL involving a join and a min

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)

Re: Tricky MySQL involving a join and a min

Thanks for this. Unfortunately my version of MySQL is pre-4.1 and
doesn't support sub-selects of this kind. Do you know of a solution
that doesn't use sub-selects?

Thanks, - Dave

Re: Tricky MySQL involving a join and a min

Doing a self join and doing a less than on the departure time works.

select c.cruise_id,c.cruise_name, s.departure_name from cruise_stops
s,cruise_stops s1,cruises c where s.departure_time < s1.departure_time and
s1.cruise_id = s.cruise_id and c.cruise_id = s.cruise_id


wrote in message
news:1141327719.439970.211970@j33g2000cwa.googlegroups.com.. .
> 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
>

Re: Tricky MySQL involving a join and a min

Does GROUP BY work on 4? If so, turn the query around...

SELECT cruises.cruise_id,cruise_name,departure_name
FROM cruise_stops
LEFT JOIN cruises ON cruise_stops.cruise_id = cruises.cruise_id
GROUP BY cruises.cruise_id
ORDER BY cruises.cruise_id

Re: Tricky MySQL involving a join and a min

I don't see how this solves the problem.

All this does is select the first cruise_stop for each cruise in the natural
order of the cruise. It doesn't find the cruise_stop for the first departure
!


"strawberry" wrote in message
news:1141486552.775111.321130@z34g2000cwc.googlegroups.com.. .
> Does GROUP BY work on 4? If so, turn the query around...
>
> SELECT cruises.cruise_id,cruise_name,departure_name
> FROM cruise_stops
> LEFT JOIN cruises ON cruise_stops.cruise_id = cruises.cruise_id
> GROUP BY cruises.cruise_id
> ORDER BY cruises.cruise_id
>