Database Reference
In-Depth Information
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
+------+----------+------+------+-------------------+-------+-------+
The preceding queries use
SELECT
*
to display all columns. To be more selective, name
only those columns in which you're interested:
mysql>
SELECT artist.name, painting.title, painting.state, painting.price
->
FROM artist INNER JOIN painting
->
ON artist.a_id = painting.a_id
->
WHERE painting.state = 'KY';
+----------+-------------------+-------+-------+
| name | title | state | price |
+----------+-------------------+-------+-------+
| Van Gogh | Starry Night | KY | 48 |
| Van Gogh | The Potato Eaters | KY | 67 |
+----------+-------------------+-------+-------+
Joins can use more than two tables. Suppose that you prefer to see complete state names
rather than abbreviations in the preceding query result. The
states
table used in earlier
chapters maps state abbreviations to names; add it to the previous query to display name
rather than abbreviation:
mysql>
SELECT artist.name, painting.title, states.name, painting.price
->
FROM artist INNER JOIN painting INNER JOIN states
->
ON artist.a_id = painting.a_id AND painting.state = states.abbrev
->
WHERE painting.state = 'KY';
+----------+-------------------+----------+-------+
| name | title | name | price |
+----------+-------------------+----------+-------+
| Van Gogh | Starry Night | Kentucky | 48 |
| Van Gogh | The Potato Eaters | Kentucky | 67 |
+----------+-------------------+----------+-------+
Another common use of three-way joins is enumerating many-to-many relationships
(see
Recipe 14.6
).
By including appropriate conditions in your joins, you can answer very specific ques‐
tions:
• Which paintings did Van Gogh paint? Use the
a_id
value to find matching rows,
add a
WHERE
clause to restrict output to rows that contain the artist name, and select
the title from those rows:
mysql>
SELECT painting.title
->
FROM artist INNER JOIN painting ON artist.a_id = painting.a_id
->
WHERE artist.name = 'Van Gogh';
+-------------------+
| title |
+-------------------+
| Starry Night |
| The Potato Eaters |
+-------------------+
