Please help on JOIN problem - Printable Version

Please help on JOIN problem - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Please help on JOIN problem (/showthread.php?tid=12106)

Please help on JOIN problem - El Forum - 10-06-2008

[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... ??????

Please help on JOIN problem - El Forum - 10-06-2008

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

Please help on JOIN problem - El Forum - 10-06-2008

[eluser]Xeoncross[/eluser]
You can also leave the

Code:
$this->db->select('*');

part off.

Please help on JOIN problem - El Forum - 10-06-2008

[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');