Select Join for two columns in codeigniter

Question

Hello everybody;

Table 1
    id product_name location added_by updated_by added date
    1  LENOVO       St 23    2        1          2016-08-26

Table 2
id first_name last_name email
1  John       Doe       jd@email.com
2  Peter      Smith     ps@email.com

I would like to know how to select data with codeigniter query in order to get result like this

product_name location added_by    updated_by
LENOVO       St 23    Peter Smith John Doe

I have tried with join but added_by column and updated_by column display same data.

Answer 1

You can used this query for your problem :

$this->db->select('t1.product_name,t1.location, CONCAT(t2_1.first_name, " ", t2_1.last_name) AS added_by, CONCAT(t2_2.first_name, " ", t2_2.last_name) AS updated_by'); 
$this->db->from('Table 1 t1');
$this->db->join('Table 2 t2_1', 't2_1.id = t1.added_by', 'left'); 
$this->db->join('Table 2 t2_2', 't2_2.id = t1.updated_by', 'left'); 
$query = $this->db->get();
return $query->result();

you can changes according your table name inside query Table 1 and Table 2