MySQL Cookbook

Referring to Join Output Column Names in Programs

Problem

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

Solution

Use column aliases to assign unique names to each column, or refer to the columns by position.

Discussion

Joins often retrieve columns from similar tables, and it’s not unusual for columns selected from different tables to have the same names. Consider again the three-way join between the shirt, tie, and pants tables that was used in Recipe 12.2:

mysql> SELECT shirt.item, tie.item, pants.item FROM shirt, tie, pants;
+-----------+--------------+----------+
| item      | item         | item     |
+-----------+--------------+----------+
| Pinstripe | Fleur de lis | Plaid    |
| Tie-Dye   | Fleur de lis | Plaid    |
| Black     | Fleur de lis | Plaid    |
| Pinstripe | Paisley      | Plaid    |
...

The query uses the table names to qualify each instance of item in the output column list to clarify which table each item comes from. But the column names in the output are not distinct, because MySQL doesn’t include table names in the column headings. If you’re processing the result of the join from within a program and fetching rows into a data structure that references column values by name, non-unique column names can cause some values to become inaccessible. The following Perl script fragment illustrates the difficulty:

$stmt = qq{ SELECT shirt.item, tie.item, pants.item FROM shirt, tie, pants }; $sth = $dbh->prepare ($stmt); $sth->execute ...