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?

Rambutan

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.

Poliomyelitis

To add to that: the reason why you should alias the columns is that the column "id" exists in both tables, resulting in a name collision. – Hick

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.

Schrock

Answer 3

Using this expression you will get results with columns id (from table sites_indexed) and id2 (alias for column id from table individual_data)

SELECT t1 . *, t2 . * FROM sites_indexed t1
LEFT JOIN (select id as id2, other_field1, other_field2 FROM individual_data) t2 ON t1.id = t2.site_id WHERE your_statement

Buxom

Answer 4

The problem is that you're using the * wildcard. If you explicitly list the column names in your query, you can give them aliases:

SELECT `sites_indexed`.`id` AS `sites_indexed_id`,
       `individual_data`.`id` AS `individual_data_id`
       FROM `sites_indexed` 
       LEFT JOIN `individual_data` ON `sites_indexed`.`id` = `individual_data`.`site_id` 
       WHERE `url` LIKE :url

Then you can reference them via the alias:

$var = $result['sites_indexed_id']; $var_b = $result['individual_data_id'];

Gaylor