zeboidlund zeboidlund - 9 months ago 43
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

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
. The actual
which represents the row for
however has the same title as

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


$var = $result['id'];

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

Anyway, how is this accomplished?


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.