CodeIgniter Forums

Full Version: Please help on JOIN problem
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]Gewa[/eluser]
Here i have a code that helps to take companies, and attach the city table
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');
                 $this->db->join('fa_user','fa_user_profile.id=fa_user.id');


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


The problem is that when lets say in the fa_user_profile table there is a row, that contains lets say city_id=4 and in city table there is no such city with ID 4, then this row is not shown in listing. How to organize the JOIN, that in such case it is joining city field to the result row with "0" result or anything else... ??????

El Forum

[eluser]GSV Sleeper Service[/eluser]
use a left join, add 'left' as a third parameter in your join statement.

El Forum

[eluser]Xeoncross[/eluser]
You can also leave the
Code:
$this->db->select('*');
part off.

El Forum

[eluser]Gewa[/eluser]
There is one thing more. if I want to join one more row, but to take value not of first row to join him all rows are gone.
Left join also will help?


f.example


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');
                 $this->db->join('fa_user','fa_user_profile.id=fa_user.id');
                 $this->db->join('region','region.id=city.parent_id');

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


see
Code:
$this->db->join('region','region.id=city.parent_id');