join query in codeigniter for two table

In this post we will show you how to join 2 or more then 2 tables with join query in codeigniter. we are using join() method for Normal Join, Join With Condition, Join with multiple tables and Join with specific type Query likeleft, right, inner, outer, right outer, left outer.

JOIN Query In Codeigniter

Hear we are use join() method to join 2 table or more then 2 tables in codeigniter model.

Normal Join Query

$this->db->select('*');
$this->db->from('school');
$this->db->join('class', 'class.id = school.id');
// execute query  
$join_query = $this->db->get();
 
/* Produces:
*  SELECT * FROM school JOIN class ON class.id = school.id
*/

Join With Condition Query

$this->db->select('*');
$this->db->from('school');
$this->db->join('class', 'class.id = school.id');
$this->db->where(array('class.id' => 17));
// execute query  
$join_query = $this->db->get();
 
/* Produces:
*  SELECT * FROM school JOIN class ON class.id = school.id 
*  where class.id = 17
*/

Join with multiple tables Query

we need to call join() method for multiple times if we need many joins method in one query.

$this->db->select('*');
$this->db->from('school');
$this->db->join('class', 'class.id = school.id');
$this->db->join('comments', 'comments.id = school.id');
// execute query  
$join_query = $this->db->get();
 
/* Produces:
*  SELECT * FROM school
*  JOIN class ON class.id = school.id
*  JOIN comments ON comments.id = school.id
*/

If you need a specific type of JOIN you can specify it via the third parameter of the function.
Options are : left, right, inner, outer, right outer, left outer

See also  install PHP Accelerator in Xampp

Join with specific type Query like :: left, right, inner, outer, right outer, left outer

You need to call join() method multiple times if you need several joins in one query.

Example – 1
For left join() method

$this->db->select('*');
$this->db->from('school');
$this->db->join('class', 'class.id = school.id','left');
$this->db->join('comments', 'comments.id = school.id','left');
// execute query 
$join_query = $this->db->get();
 
/* Produces:
*  SELECT * FROM school
*  LEFT JOIN class ON class.id = school.id
*  LEFT JOIN comments ON comments.id = school.id
*/

Example – 2
For right join() method

$this->db->select('*');
$this->db->from('school');
$this->db->join('class', 'class.id = school.id','right');
$this->db->join('comments', 'comments.id = school.id','right');
// execute query 
$join_query = $this->db->get();
 
/* Produces:
*  SELECT * FROM school
*  RIGHT JOIN  class ON class.id = school.id
*  RIGHT JOIN  comments ON comments.id = school.id
*/

Leave a Comment