Database Reference
In-Depth Information
Combining Tables
So far in this chapter we'vebeen working with just one table. Let's look at some ways to
select data from more than one table. To do this, we will have to tell MySQL the tables
from which we want data and how to join them together.
For an example, let's get a list of birds with their family names. To keep the query simple,
we'll select birds from different families, but all in the same order of birds. In earlier ex-
amples where we got a list of shore birds, they all had the same
order_id
of 102. We'll
use that value again. Enter this
SELECT
statement on your server:
SELECT common_name AS 'Bird',
bird_families.scientific_name AS 'Family'
FROM birds, bird_families
WHERE birds.family_id = bird_families.family_id
AND order_id = 102
AND common_name != ''
ORDER BY common_name LIMIT 10;
+------------------------+------------------+
| Bird | Family |
+------------------------+------------------+
| African Jacana | Jacanidae |
| African Oystercatcher | Haematopodidae |
| African Skimmer | Laridae |
| African Snipe | Scolopacidae |
| Aleutian Tern | Laridae |
| Amami Woodcock | Scolopacidae |
| American Avocet | Recurvirostridae |
| American Golden-Plover | Charadriidae |
| American Oystercatcher | Haematopodidae |
| American Woodcock | Scolopacidae |
+------------------------+------------------+
This
SELECT
statement returns one column from the
birds
table and one from the
bird_families
table. This is a hefty SQL statement, but don't let it fluster you. It's like
previous statements in this chapter, but with some minor changes and one significant one.
First, let's focus on the one significant change: how we've drawn data from two tables.
The
FROM
clause lists the two tables, separated by a comma. In the
WHERE
clause, we in-
dicated that we want rows in which the value of
family_id
in the two tables is equal.
Otherwise, we would have duplicate rows in the results. Because those columns have the
same name (
family_id
) in both tables, to prevent ambiguity, we put the table name be-
fore the colum name, separated by a dot (e.g.,
birds.family_id
). We did the same
