Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata