codeigniter select join twice from same table
I have 3 tables, where is the first one for user. In another two I have id_user, for product who created or activation who created. How I can show all data from these two tables with both username? I figure out how to join 2 tables, but third I have join twice and I don't know how. This is example for two tables:
$query=$this->db->select('*') ->from('activation') ->join('products','products.id_pro = activation.id_pro') ->order_by('id_key','DESC') ->get();
Table activation has column user_a with user id, and product has column user_p with user id. Sometimes is same user sometimes not. Any helps?
Heres how I do it using aliases. You can format it your way too.
$this->db->select('tb1.*,tb2.*,u.*'); $this->db->join('table1name tb1','tb1.id = u.tb1_id'); $this->db->join('table2name tb2','tb2.id = u.tb2_id'); $this->db->order_by('u.id_key','DESC'); $result = $this->db->get('User u')->result();
If you want to alter between left and right join just add 'left' or 'right' to the join function.
$this->db->join('table1name pr1','pr1.id = u.tb1_id','right'); $this->db->join('table2name tb2','tb2.id = u.tb2_id','right');
This would preference your user table.