Database Reference
In-Depth Information
| 6 | 9 |
+----------+----------+
You'll surely notice that the content of the
movies_actors_link
table is entirely mean‐
ingless from a human perspective. That's okay: we need never display it explicitly. Its
utility derives from its ability to link the two primary tables in queries, without appearing
in query output itself. The next few examples illustrate this principle. They answer
questions about the movies or actors, using three-way joins that relate the two primary
tables using the link table.
• List all the pairings that show each movie and who acted in it. This statement enu‐
merates all the correspondences between the
movie
and
actor
tables and repro‐
duces the information that was originally in the nonnormal
movies_actors
table:
mysql>
SELECT m.year, m.movie, a.actor
->
FROM movies AS m INNER JOIN movies_actors_link AS l
->
INNER JOIN actors AS a
->
ON m.id = l.movie_id AND a.id = l.actor_id
->
ORDER BY m.year, m.movie, a.actor;
+------+----------------------------+---------------+
| year | movie | actor |
+------+----------------------------+---------------+
| 1997 | The Fifth Element | Bruce Willis |
| 1997 | The Fifth Element | Gary Oldman |
| 1997 | The Fifth Element | Ian Holm |
| 1999 | The Phantom Menace | Ewan McGregor |
| 1999 | The Phantom Menace | Liam Neeson |
| 2001 | The Fellowship of the Ring | Elijah Wood |
| 2001 | The Fellowship of the Ring | Ian Holm |
| 2001 | The Fellowship of the Ring | Ian McKellen |
| 2001 | The Fellowship of the Ring | Orlando Bloom |
| 2005 | Kingdom of Heaven | Liam Neeson |
| 2005 | Kingdom of Heaven | Orlando Bloom |
| 2010 | Red | Bruce Willis |
| 2010 | Red | Helen Mirren |
| 2011 | Unknown | Diane Kruger |
| 2011 | Unknown | Liam Neeson |
+------+----------------------------+---------------+
• List the actors in a given movie:
mysql>
SELECT a.actor
->
FROM movies AS m INNER JOIN movies_actors_link AS l
->
INNER JOIN actors AS a
->
ON m.id = l.movie_id AND a.id = l.actor_id
->
WHERE m.movie = 'The Fellowship of the Ring'
->
ORDER BY a.actor;
+---------------+
| actor |
+---------------+
| Elijah Wood |
| Ian Holm |
