MySQL Forums
Forum List  »  Newbie

Re: Query result - not to be sorted asc or desc
Posted by: Jay Pipes
Date: July 25, 2005 08:39AM

The solution to this would be to have each tour route tied to an ordered list of locations. For instance, assume a simple tour table:

CREATE TABLE tours (
tour_id SMALLINT UNSIGNED NOT NULL
, name VARCHAR(50) NOT NULL
, PRIMARY KEY (tour_id)
);

then a "key map" table to tie the locations with the tour table:

CREATE TABLE tour_routes (
tour SMALLINT UNSIGNED NOT NULL
, location CHAR(1) NOT NULL
, order_in_route SMALLINT UNSIGNED NOT NULL
, PRIMARY KEY (tour, location)
);

you would then determine the order of each tour via the key map table:

SELECT l.location, l.notes
FROM locations l
INNER JOIN tour_routes tr
ON tr.tour = 123
AND l.location = tr.location
ORDER BY tr.order_in_route;

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote


Subject
Written By
Posted
Re: Query result - not to be sorted asc or desc
July 25, 2005 08:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.