MySQL Cookbook, 2nd Edition

Referring to Join Output Column Names in Programs

Problem

You need to process the result of a join from within a program, but the column names in the result set aren’t unique.

Solution

Revise the query using column aliases so that each column has a unique name, or refer to the columns by position.

Discussion

Joins typically retrieve columns from related tables, so 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 (originally seen in Finding Rows in One Table That Match Rows in Another). For each painting, it displays artist name, painting title, the state in which you acquired the item, and how much it cost:

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 | The Mona Lisa     | Michigan |    87 |
| Van Gogh | Starry Night      | Kentucky |    48 |
| Van Gogh | The Potato Eaters | Kentucky |    67 |
| Van Gogh | The Rocks         | Iowa     |    33 |
| Renoir   | Les Deux Soeurs   | Nebraska |    64 |
+----------+-------------------+----------+-------+

The statement is written using table qualifiers for each output column. Nevertheless, the column names in the output are not distinct because ...