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
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
*/
