How to rename table row while joining

[eluser]Gewa[/eluser]
Hi, I have such problem in my controller. When I take an info of company and join city
Both of them have in the table row “id”
How to rename the ID in City and Region tables while joining them?

Here is part of code

Code:

$this->db->select('*');
                 $this->db->from('fa_user_profile');
                 $this->db->where('type','company');
                 $this->db->join('city','fa_user_profile.city_id=city.id','left');
                 $this->db->join('fa_user','fa_user_profile.id=fa_user.id','left');
                 $this->db->join('t_categories','fa_user_profile.subcat_id=t_categories.subcat_id','left');
                 $this->db->join('t_main_category','fa_user_profile.cat_id=t_main_category.cat_id','left');



                 $query=$this->db->get();
                 $qanak=$query->num_rows();

[eluser]Randy Casburn[/eluser]
Hi Gewa,

The objective is to construct your query so your select statement end up with you alias ending with AS like this:

SELECT city.id AS city_id, company.id AS company_id FROM ...

So you have to be a little more explicit with your SELECT statement that a "splat".

Hope that helps.

Randy

[eluser]Gewa[/eluser]
This i know, but in Active Record way I dont understand how to make it...


Can you give a code example or edited code if mine , please?

[eluser]ontguy[/eluser]
From Randy's example, it would look like this in Active Record:

Code:

$this->db->select('city.id AS city_id, company.id AS company_id');

[eluser]Randy Casburn[/eluser]
Thanks ontguy!

@Gewa you'll also need the rest of you column names since you've replaced the 'splat'.

Randy