How to identify fields from different tables with the same name in a JOIN query result?

Question

Hi there,

I'm running a query in MYSQL using LEFT JOIN to get users and their user groups. Both tables have columns named id. My result is being returned in an array (PHP) so that I have one array with all field data, but also only one array["id"].

Here's my query so far:

SELECT usr.id, usg.id FROM users usr LEFT JOIN (user_groups usg) ON (usg.id=usr.group_id)

Now the easy solution would be to rename the fields to unique names like usr_id and usg_id but I prefer not to. I would much rather have my values returned to the array as array["usr.id"] automatically.

(I'm using the CodeIgniter framework which automatically does the rest, but I could rewrite some stuff there if only MySQL could return the table alias with it)

Answer 1

use the 'as' keyword

select table1.id as table1Id, table2.id as table2Id
from table1
join table2
on somecondition

Answer 2

just tell mysql that you want to do an on the fly rename using "as 'newname'"

SELECT usr.id as 'user_id', usg.id FROM users usr LEFT JOIN (user_groups usg) ON (usg.id=usr.group_id)

or using doctrine itself do it DQR style:

$resultArray = Doctrine_Query::create()
                        ->select('usr.id, usg.id')
                        ->from('users usr')
                        ->leftJoin('usr.user_groups usg')
                        ->setHydrationMode(Doctrine::HYDRATE_ARRAY)
                        ->execute();

this should return an array where you can access both id's like that:

$resultArr['id']; //for accessing the usr.id

and eiter one of these both depending on the association you made between users and user_groups

$resultArr['user_groups']['id']; //for accessing the usg.id if it is one to one
$resultArr['user_groups'][0]['id']; //for accessing the usg.id if it is a one to many