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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.