zeboidlund zeboidlund - 5 months ago 21
MySQL Question

Alias a column name on a left join

Let's say I have two tables, and both their primary identifiers use the name 'id'. If I want to perform a join with these two tables, how would I alias the

id
of the table that I want to join with the former table?

For example:

SELECT * FROM `sites_indexed` LEFT JOIN `individual_data` ON `sites_indexed`.`id` = `individual_data`.`site_id` WHERE `url` LIKE :url


Now, site_id is supposed to link up with
sites_indexed.id
. The actual
id
which represents the row for
individual_data
however has the same title as
sites_indexed
.

Personally, I like to just use the name
id
for everything, as it keeps things consistent. When scripting server-side however, it can make things confusing.

e.g.

$var = $result['id'];


Given the aforementioned query, wouldn't this confuse the interpreter?

Anyway, how is this accomplished?

Answer

Instead of selecting all fields with "SELECT *" you should explicitly name each field you need, aliasing them with AS as required. For example:

SELECT si.field1 as si_field1,
       si.field2 as si_field2,
       ind_data.field1 as ind_data_field1
  FROM sites_indexed as si
  LEFT JOIN individual_data as ind_data 
         ON si.id = ind_data.site_id 
 WHERE `url` LIKE :url

And then you can reference the aliased names in your result set.