Posted by Simon on 06/17/16 11:41

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


<laredotornado@zipmail.com> 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
>

• Next in forum: Re: Tricky MySQL involving a join and a min
• Prev in forum: Re: Tricky MySQL involving a join and a min
• Thread view: Re: Tricky MySQL involving a join and a min

[Reply to this message]