Jeff Sabarese wrote:
> it's embarrassing to say, but i'm not sure what
> you mean by "use the \G output option". i looked
> around in the manual, but couldn't find anything
> other that what looked to me to be an alternative
> line ending to ";", however, when i ended my query
> w/ "\G", i received an error. could you please
> show me an example?
No problem at all. The \G command is used in the mysql command line client to change the output from a table-based result to a more readable (at least on these forums) one-row-per-field output. But, no big deal. Your HTML output in the links works just fine :)
As for the schema, the first thing we want to do is normalize the table structure -- after that, any query is possible. Your schema has two fatal flaws currently; we'll work to address each. First, there is currently no way to relate the two tables, as the *field names* of one table represent the *key values* of the other. What we need is a schema where the key values are contained in the row/field values themselves. More on that below.
Secondly, each table has fallen prey to a normalization faux-pas known as non-descriptive attributes, meaning the field in the table does not describe the entity for which each row in the table represents. As an example, in your stops table, you have a field called fare_free (ENUM('yes','no')). This attribute (fare_free) actually describes the entire *route*, no? It doesn't actually describe the stop itself. We need to move these fields into other tables which house the entities that they describe. If no such table exists, we need to create the table.
I noticed that you don't have a Buses table. This might be a good place to start. We'll start simple:
CREATE TABLE Buses (
bus_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
, name VARCHAR(80) NOT NULL # a name for the bus
, max_passengers TINYINT UNSIGNED NOT NULL # an example field we'll look at below
, PRIMARY KEY pk_Buses (bus_id)
);
Next, we'll tackle the route table, which is also missing, but critical in normalizing the schema:
CREATE TABLE Routes (
route_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
, name VARCHAR(80) NOT NULL # a descriptive name for the route... like "New York to Philly Express #1"
, bus_assigned SMALLINT UNSIGNED NOT NULL
, fare_free ENUM('Yes','No') NOT NULL DEFAULT 'No'
, PRIMARY KEY pk_Routes (route_id)
);
The bus_assigned field stores the ID of the bus which is assigned to the route, and enables us to have a one-to-many relationship between the Buses and Routes table.
OK, now that we've got our simple Routes table, let's re-design the Stops table to properly describe the *Stop*, not the route. Here's an example, with some of the fields from your table:
CREATE TABLE Stops (
stop_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
, name VARCHAR(80) NOT NULL # a descriptive name for the stop ... like "Grand Central Station"
, neighborhood VARCHAR(80) NOT NULL # again, a description
, remarks TEXT NULL
, PRIMARY KEY pk_Stops (stop_id)
);
Notice, that I've specifically left out the following original fields: near_transfer, miles2next, elapsed2next, mins2main. We'll cover those fields in a second.
The next table we want to tackle is the TimeTable table. This table will store the relationship between the *Route* and the *Stop*. In other words, it will contain the sequence of stops, from first to last, that the route entails, along with other descriptive attributes about each "leg" of the route, such as the arrival time and time it takes to get to the next stop in the route.
The biggest difference to note is that we've normalized the schema so that instead of each route being on a single row, we split the route into one or more rows, with each row containing a unique Route/Stop combination:
CREATE TABLE RouteStops (
route_id SMALLINT UNSIGNED NOT NULL
, stop_id SMALLINT UNSIGNED NOT NULL
, arrival_time TIME NOT NULL
, miles2next SMALLINT UNSIGNED NOT NULL
, elapse2next SMALLINT UNSIGNED NOT NULL
, PRIMARY KEY pk_RouteStops (route_id, stop_id)
);
Going back to your original stops table, you had a field called near_transfer VARCHAR(80). If I'm not mistaken, you meant for this field to store a string describing to passengers which other stops they could transfer to/from at this stop, no?
The way to do this in SQL is to create a separate table which stores the Stops that have transfer capacity from one stop to another:
CREATE TABLE Stop2Stop (
stop SMALLINT UNSIGNED NOT NULL
, transfer_stop SMALLINT UNSIGNED NOT NULL
, PRIMARY KEY pk_Stop2Stop (stop, transfer_stop)
);
If we knew that passengers could transfer from Stop 1 to Stop 10,32, and 54 easily, we could put the following in our Stop2Stop table:
INSERT INTO Stop2Stop VALUES (1,10),(1,32),(1,54);
Getting a list of transferrable stops given a stop ID is then easy:
SELECT transfer_stop FROm Stop2Stop WHERE stop = 1;
Now that our database is normalized, we can use standard SQL to do many more things. For instance, print route #44 in order of the time of day:
SELECT s.name, rs.arrival_time
FROM RouteStops rs
INNER JOIN Stops s
ON rs.stop_id = s.stop_id
WHERE s.route_id = 44
ORDER BY rs.arrival_time ASC;
we can even add a list of transferable stations to our output, now that we have normalized our data:
SELECT
s.name
, rs.arrival_time
, GROUP_CONCAT(s2s.transfer_stop SEPARATOR ', ') as can_transfer_to
FROM RouteStops rs
INNER JOIN Stops s
ON rs.stop_id = s.stop_id
INNER JOIN Stop2Stop s2s
ON rs.stop_id = s2s.stop
WHERE s.route_id = 44
GROUP BY s.name, rs.arrival_time
ORDER BY rs.arrival_time ASC;
Let's say we wanted to get a list of routes, the assigned bus, the number of passengers each route could take, and the number of stops in each route:
SELECT
r.route_id
, r.name as route
, b.name as bus
, b.max_passengers
, COUNT(*) as num_stops
FROM Routes r
INNER JOIN Buses b
ON r.bus_assigned = b.bus_id
INNER JOIN RouteStops rs
ON r.route_id = rs.route_id
GROUP BY r.route_id;
The possibilities are endless; I'll leave you to continue on. Post back if you have more questions. Oh, and I just kind of whipped this up because I was busy working on something else. I haven't tested the code, so if there are a few syntax errors, forgive me :)
Cheers,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com