Hi All,
Here is a bit more detail into the problem with some sample tables to
help out. Below are tables that mimic the design of what I am using.
That may help finding our what I'm doing wrong.
--------------------------------------------------------------
ITINERARIES
itin_id nights cline destination ship
8451 8 1 2 1
2765 12 2 1 3
DESTINATIONS
dest_id destination
1 Bahamas
2 Europe
3 Mexico
SHIPS
ship_id ship
1 Floater
2 Sinker
3 Semi-floater
CRUISE_LINES
cline_id cruise_line
1 Carnival
2 Celebrity
SAILINGS
sailing_id itin_id sailing_date
3435 8451 2006-02-12
1987 2765 2006-03-02
9675 8451 2006-01-31
6723 8451 2006-12-10
3498 2765 2006-02-20
-------------------------------------------------------------
Here is the query that does not pull up the dates in the correct order:
-------------------------------------------------------------
SELECT i.itin_id, d.destination, i.nights,
DATE_FORMAT(MIN(sailing_date), '%b %e, %Y') AS minsd
FROM itineraries AS i, destinations AS d, ships AS s, cruise_lines AS
cl, sailings AS sa
WHERE i.destination = d.dest_id AND i.cline = cl.cline_id AND i.ship =
s.ship_id AND i.itin_id = sa.itin_id
GROUP BY i.itin_id
ORDER BY destination, sailing_date
-------------------------------------------------------------
The result I am trying to get would be this:
-------------------------------------------------------------
itin_id destination nights minsd
2765 Bahamas 12 2006-02-20
8451 Europe 8 2006-01-31
-------------------------------------------------------------
Does any of this look close or am I way off?
If any one has any ideas, let me know. All I can think to do right now
is to possibly make a temporary table before this query is made and
incorporate that table into this query.
Thanks for you help,
Mark
----------------------------------------
On Jan 16, 2006, at 4:15 PM, Mark Wheeler wrote:
Hi,
I don't know if I am giving you enough information to diagnose the
problem, but here goes. I am trying to sort some dates, but they seem
to not be sorting correctly so, for example, February 23, 2006 comes
before February 11, 2006. Here is the query:
-----------------------------------------------------------
SELECT i.itin_id, d.destination, i.nights,
DATE_FORMAT(MIN(sailing_date), '%b %e, %Y') AS minsd
FROM itineraries AS i, destinations AS d, ships AS s, cruise_lines AS
cl, sailings AS sa
WHERE i.destination = d.dest_id AND i.cline = cl.cline_id AND i.ship =
s.ship_id AND i.itin_id = sa.itin_id
GROUP BY i.itin_id
ORDER BY destination, sailing_date
------------------------------------------------------------
The "MIN()" call is supposed to go through the dates associated with
the "i.itin_id" (they are stored in a different table) and find the
earliest date. I'm at a bit of a loss.
Does any one have any idea?
Thanks,
Mark
The php_mysql group is dedicated to learn more about the PHP/MySQL web
database possibilities through group learning.
YAHOO! GROUPS LINKS
▪ Visit your group "php_mysql" on the web.
▪ To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
▪ Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service.
The php_mysql group is dedicated to learn more about the PHP/MySQL web database
possibilities through group learning.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php_mysql/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
