Database Reference
In-Depth Information
Now that we have entered data for a few more birds, let's connect a few of our tables to-
gether and retrieve data from them. We'll use a
SELECT
statement, but we'll give a list of
the tables to merge the data in the results set. This is much more complicated than any of
the previous
SELECT
statements, but I want you to see the point of creating different
tables, especially the reference tables we have created. Try entering the following SQL
statement on your server:
SELECT common_name AS 'Bird',
birds.scientific_name AS 'Scientific Name',
bird_families.scientific_name AS 'Family',
bird_orders.scientific_name AS 'Order'
FROM birds,
bird_families,
bird_orders
WHERE birds.family_id = bird_families.family_id
AND bird_families.order_id = bird_orders.order_id;
+-----------------------+----------------------+--------------+---------------+
| Bird | Scientific Name | Family |
Orders |
+-----------------------+----------------------+--------------+---------------+
| Mountain Plover | Charadrius montanus | Charadriidae |
Ciconiiformes |
| Snowy Plover | Charadrius alex... | Charadriidae |
Ciconiiformes |
| Black-bellied Plover | Pluvialis squatarola | Charadriidae |
Ciconiiformes |
| Pacific Golden Plover | Pluvialis fulva | Charadriidae |
Ciconiiformes |
+-----------------------+----------------------+--------------+---------------+
In this
SELECT
statement, weare connecting together three tables. Before looking at the
columns selected, let's look at the
FROM
clause. Notice that all three tables are listed, sep-
arated by commas. To assist you in making sense of this statement, I've added some in-
denting. The table names don't need to be on separate lines, as I have laid them out.
MySQL strings these three tables together basedon the
WHERE
clause. First, we're telling
MySQL to join the
birds
table to the
bird_families
table where the
family_id
from both tables equal or match. Using
AND
, wethen give another condition in the
WHERE
clause. We tell MySQL to join the
bird_families
table to the
bird_orders
table where the
order_id
from both tables are equal.
That may seem pretty complicated, but if you had a sheet of paper in front of you showing
thousands of birds, and a sheet of paper containing a list of bird families, and another
