[thelist] Tip: Double aliasing tables in MySQL

Frank lists at frankmarion.com
Mon Aug 18 02:38:01 CDT 2003


<tip type="MySQL" author="Frank Marion">

Did you know that you can alias a table more than once in the same query?* 
I had to edit some of my first work, done years ago to handle changes to a 
database and I discovered that a table can be aliased more than once with 
apparently little or no penalty in speed or robustness.

SELECT
   p.ProductID
, p.ProductName
, p.BrandID
, p.CategoryID
, b.cat_id AS BrandID
, b.cat_name AS BrandName
, c.cat_id
, c.cat_name
FROM Products p

LEFT JOIN categories b
  ON p.BrandID = b.cat_id

LEFT JOIN categories c
  ON p.CategoryID = c.cat_id

WHERE [... and so on and so forth ...]

This was a quick and easy fix to my problem, saving me a couple of hours of 
tedious search and replace.

*I can only attest that this works in MySQL 4.x

</tip>

* And I get the feeling that Rudy is going to jump all over my butt over this.



--
Frank Marion     lists at frankmarion.com      Keep the signal high.  



More information about the thelist mailing list