Build Your Own Database-Driven Website Using PHP MySQL [Electronic resources] نسخه متنی

Column and Table Name Aliases

In some situations,
it may be more convenient to be able to refer to MySQL columns and tables
using different names. Let's take the example of a database used by an airline's
online booking system; this example actually came up in the SitePoint Forums.
The database structure can be found in airline.sql in
the code archive if you want to follow along.

To represent the flights offered by the airline, the database contains
two tables: Flights and Cities.
Each entry in the Flights table represents
an actual flight between two cities—the origin and destination of the
flight. Obviously, Origin and Destination are columns in the Flights table,
with other columns for things like the date and time of the flight, the type
of aircraft, the flight numbers, and the various fares.

The Cities table contains a list
of all the cities to which the airline flies. Thus, both the Origin and Destination columns in the Flights table
will just contain IDs referring to entries in the Cities table.
Now, consider these queries. To retrieve a list of flights with their origins:

mysql>SELECT Flights.Number, Cities.Name
    ->FROM Flights, Cities
    ->WHERE Flights.Origin = Cities.ID;
+--------+-----------+
| Number | Name      |
+--------+-----------+
| CP110  | Montreal  |
| CP226  | Sydney    |
| QF2026 | Melbourne |
...      ...

To obtain a list of flights with their destinations:

mysql>SELECT Flights.Number, Cities.Name
    ->FROM Flights, Cities
    ->WHERE Flights.Destination = Cities.ID;
+--------+----------+
| Number | Name     |
+--------+----------+
| CP110  | Sydney   |
| CP226  | Montreal |
| QF2026 | Sydney   |
...      ...

Now, what if we wanted to list both the origin and destination of each
flight with a single query? That's pretty reasonable, right? Here's a query
you might try:

mysql>SELECT Flights.Number, Cities.Name, Cities.Name
    ->FROM Flights, Cities
    ->WHERE Flights.Origin = Cities.ID
    ->AND Flights.Destination = Cities.ID;
Empty set (0.01 sec)

Why doesn't this work? Have another look at the query, and this time
focus on what it actually says, rather than what you expect it to do. It tells
MySQL to join the Flights and Cities tables and list the flight number, city name,
and city name (yes, twice!) of all entries obtained, by matching up the Origin with the city ID and
the Destination with the city ID. In other words, the Origin, Destination, and city ID must
all be equal! This results in a list of all flights where the origin and the
destination are the same! Unless your airline offers scenic flights, there
aren't likely to be any entries that match this description (thus the "Empty
set" result above).

What we need is a way to be able to return two different entries from
the Cities table, one for the origin and
one for the destination, for each result. If we had two copies of the table,
one called Origins and one called Destinations, this would be much easier to do, but
why maintain two tables that contain the same list of cities? The solution
is to give the Cities table two different
temporary names (aliases) for the purposes of this
query.

If we follow the name of a table with AS Alias in
the FROM portion of the SELECT query,
we can give it a temporary name with which we can refer to it elsewhere in
the query. Here's that first query again (to display flight numbers and origins
only), but this time we've given the Cities table
an alias: Origins.

mysql>SELECT Flights.Number, Origins.Name
    ->FROM Flights, Cities AS Origins
    ->WHERE Flights.Origin = Origins.ID;

This doesn't actually change the way the query works—in fact,
it doesn't change the results at all—but for long table names, it can
save some typing. Consider, for example, if we had given aliases of F and O to Flights and Cities,
respectively. The query would be much shorter as a result.

Let's now return to our problem query. If we refer to the Cities table twice, using two different aliases,
we can use a three-table join (where two of the tables are actually one and
the same) to get the effect we want:

mysql>SELECT Flights.Number, Origins.Name,
    ->  Destinations.Name
    ->FROM Flights, Cities AS Origins,
    ->  Cities AS Destinations
    ->WHERE Flights.Origin = Origins.ID
    ->AND Flights.Destination = Destinations.ID;
+--------+-----------+----------+
| Number | Name      | Name     |
+--------+-----------+----------+
| CP110  | Montreal  | Sydney   |
| CP226  | Sydney    | Montreal |
| QF2026 | Melbourne | Sydney   |
...      ...         ...

You can also define aliases for column names. We could use this, for
example, to differentiate the two Name columns
in our result table above:

mysql>SELECT F.Number, O.Name AS Origin,
    ->  D.Name AS Destination
    ->FROM Flights AS F, Cities AS O, Cities AS D
    ->WHERE F.Origin = O.ID AND F.Destination = D.ID;
+--------+-----------+-------------+
| Number | Origin    | Destination |
+--------+-----------+-------------+
| CP110  | Montreal  | Sydney      |
| CP226  | Sydney    | Montreal    |
| QF2026 | Melbourne | Sydney      |
...      ...         ...