Alias a column name on a left join

Question

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 1

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.

Answer 2

This thread is old and i found because i had the same problem. Now i have a better solution. The answer given by Paul McNett and antun forces you to list all fields but in some cases this is impossible (too much fields to list), so you can keep the * and alias only the fields you want (typically the fields that have the same name and will override each other).

Here's how :

    SELECT *, t.myfield as myNewName 
FROM table t ... continue your query

you can add as much aliases as you want by adding comas.