Database Reference
In-Depth Information
To avoid using the temporary table, select the same rows using a subquery in the
FROM
clause:
SELECT
tmp
.
driver_miles
,
driver_log
.
*
FROM
driver_log
INNER
JOIN
(
SELECT
name
,
SUM
(
miles
)
AS
driver_miles
FROM
driver_log
GROUP
BY
name
)
AS
tmp
ON
driver_log
.
name
=
tmp
.
name
ORDER
BY
tmp
.
driver_miles
DESC
,
driver_log
.
trav_date
;
14.10. Referring to Join Output Column Names in
Programs
Problem
You need to process the result of a join from within a program, but column names in
the result set aren't unique.
Solution
Rewrite the query using column aliases so that each column has a unique name. Alter‐
natively, refer to the columns by position.
Discussion
Joins typically retrieve columns from related tables and it's not unusual for columns
selected from different tables to have the same names. Consider the following join that
shows the items in your art collection. For each painting, it displays artist name, painting
title, the state in which you acquired the item, and its price:
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;
+----------+-------------------+----------+-------+
| name | title | name | price |
+----------+-------------------+----------+-------+
| Da Vinci | The Last Supper | Indiana | 34 |
| Da Vinci | Mona Lisa | Michigan | 87 |
| Van Gogh | Starry Night | Kentucky | 48 |
| Van Gogh | The Potato Eaters | Kentucky | 67 |
| Renoir | Les Deux Soeurs | Nebraska | 64 |
+----------+-------------------+----------+-------+
The statement uses table qualifiers for each output column, but MySQL doesn't include
table names in the column headings, so not all column names in the output are distinct.
If you process the join result from within a program and fetch rows into a data structure
