Database Reference
In-Depth Information
A tip-off that a self-join is required is that you want to know which pairs of rows in a
table satisfy some condition. Suppose that your favorite painting is
The Potato Eaters
,
and you want to identify all items in your collection that were painted by the same artist.
The artist ID and painting titles that we begin with look like this:
mysql>
SELECT a_id, title FROM painting ORDER BY a_id;
+------+-------------------+
| a_id | title |
+------+-------------------+
| 1 | The Last Supper |
| 1 | Mona Lisa |
| 3 | Starry Night |
| 3 | The Potato Eaters |
| 4 | Les Deux Soeurs |
+------+-------------------+
Solve the problem as follows:
1. Identify which
painting
table row contains the title
The Potato Eaters
, so that you
can refer to its
a_id
value.
2. Match other rows in the table that have the same
a_id
value.
3. Display the titles from those matching rows.
The trick lies in using the proper notation. First attempts at joining a table to itself often
look something like this:
mysql>
SELECT title
->
FROM painting INNER JOIN painting
->
ON a_id = a_id;
->
WHERE title = 'The Potato Eaters';
ERROR 1066 (42000): Not unique table/alias: 'painting'
The column references in that statement are ambiguous because MySQL cannot tell
which instance of the
painting
table any given column name refers to. The solution is
to alias at least one instance of the table so that you can distinguish column references
by using different table qualifiers. The following statement shows how to do this, using
the aliases
p1
and
p2
to refer to the
painting
table different ways:
mysql>
SELECT p2.title
->
FROM painting AS p1 INNER JOIN painting AS p2
->
ON p1.a_id = p2.a_id
->
WHERE p1.title = 'The Potato Eaters';
+-------------------+
| title |
+-------------------+
| Starry Night |
| The Potato Eaters |
+-------------------+
