Database Reference
In-Depth Information
-> FROM artist INNER JOIN painting
-> ON painting.a_id = artist.a_id
-> WHERE painting.price = @max_price;
+----------+-----------+-------+
| name | title | price |
+----------+-----------+-------+
| Da Vinci | Mona Lisa | 87 |
+----------+-----------+-------+
The same thing can be done by creating a temporary table to hold the maximum price
and joining it with the other tables:
CREATE TABLE tmp SELECT MAX ( price ) AS max_price FROM painting ;
SELECT artist . name , painting . title , painting . price
FROM artist INNER JOIN painting INNER JOIN tmp
ON painting . a_id = artist . a_id
AND painting . price = tmp . max_price ;
On the face of it, using a temporary table and a join is just a more complicated way of
answering the question than with a user-defined variable. Does this technique have any
practical value? Yes, it does, because it leads to a more general technique for answering
more difficult questions. The previous statements show information only for the single
most expensive painting in the entire painting table. What if your question is, “What
is the most expensive painting for each artist ?” You can't use a user-defined variable to
answer that question because the answer requires finding one price per artist, and a
variable holds only a single value. But the technique of using a temporary table works
well because the table can hold multiple rows, and a join can find matches for all of
them.
To answer the question, select each artist ID and the corresponding maximum painting
price into a temporary table. This table contains not only the maximum painting price
but the maximum within each group, where “group” is defined as “paintings by a given
artist.” Then use the artist IDs and prices stored in the temporary table to match rows
in the painting table, and join the result with the artist table to get the artist names:
mysql> CREATE TABLE tmp
-> SELECT a_id, MAX(price) AS max_price FROM painting GROUP BY a_id;
mysql> SELECT artist.name, painting.title, painting.price
-> FROM artist INNER JOIN painting INNER JOIN tmp
-> ON painting.a_id = artist.a_id
-> AND painting.a_id = tmp.a_id
-> AND painting.price = tmp.max_price;
+----------+-------------------+-------+
| name | title | price |
+----------+-------------------+-------+
| Da Vinci | Mona Lisa | 87 |
| Van Gogh | The Potato Eaters | 67 |
| Renoir | Les Deux Soeurs | 64 |
+----------+-------------------+-------+
Search WWH ::




Custom Search