3.3.4.9. Using More Than one Table
The pet table keeps track of which pets you
have. If you want to record other information about them, such
as events in their lives like visits to the vet or when
litters are born, you need another table. What should this
table look like? It needs:
To contain the pet name so that you know which animal each
event pertains to.
A date so that you know when the event occurred.
A field to describe the event.
An event type field, if you want to be able to categorize
events.
Given these considerations, the CREATE
TABLE statement for the event
table might look like this:
mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
-> type VARCHAR(15), remark VARCHAR(255));
As with the pet table, it's easiest to load
the initial records by creating a tab-delimited text file
containing the information:
Load the records like this:
mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
Based on what you have learned from the queries that you have
run on the pet table, you should be able to
perform retrievals on the records in the
event table; the principles are the same.
But when is the event table by itself
insufficient to answer questions you might ask?
Suppose that you want to find out the ages at which each pet
had its litters. We saw earlier how to calculate ages from two
dates. The litter date of the mother is in the
event table, but to calculate her age on
that date you need her birth date, which is stored in the
pet table. This means the query requires
both tables:
mysql> SELECT pet.name,
-> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
-> remark
-> FROM pet, event
-> WHERE pet.name = event.name AND event.type = 'litter';
+--------+------+-----------------------------+
| name | age | remark |
+--------+------+-----------------------------+
| Fluffy | 2 | 4 kittens, 3 female, 1 male |
| Buffy | 4 | 5 puppies, 2 female, 3 male |
| Buffy | 5 | 3 puppies, 3 female |
+--------+------+-----------------------------+
There are several things to note about this query:
The FROM clause lists two tables
because the query needs to pull information from both of
them.
When combining (joining) information from multiple tables,
you need to specify how records in one table can be
matched to records in the other. This is easy because they
both have a name column. The query uses
WHERE clause to match up records in the
two tables based on the name values.
Because the name column occurs in both
tables, you must be specific about which table you mean
when referring to the column. This is done by prepending
the table name to the column name.
You need not have two different tables to perform a join.
Sometimes it is useful to join a table to itself, if you want
to compare records in a table to other records in that same
table. For example, to find breeding pairs among your pets,
you can join the pet table with itself to
produce candidate pairs of males and females of like species:
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1, pet AS p2
-> WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
+--------+------+--------+------+---------+
| name | sex | name | sex | species |
+--------+------+--------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
| Buffy | f | Bowser | m | dog |
+--------+------+--------+------+---------+
In this query, we specify aliases for the table name to refer
to the columns and keep straight which instance of the table
each column reference is associated with.
