CodeIgniter – Join Query

CodeIgniter Join query function is used to fetch the result from one or more tables. The join types are normal join, inner join, left join and right join.

Normal Join

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.typee');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid');
$query=$this->db->get();
$data=$query->result_array();

SELECT `tbl_user`.`username`, `tbl_user`.`userid`, `tbl_usercategory`.`type` 
FROM (`tbl_user`) 
JOIN `tbl_usercategory` ON `tbl_usercategory`.`usercategoryid`=`tbl_user`.`usercategoryid`

Join With Condition

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.typee');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid');
$this->db->where('tbl_usercategory','admin');
$query=$this->db->get();
$data= $query->result_array();

		
SELECT `tbl_user`.`username`, `tbl_user`.`userid`, `tbl_usercategory`.`type` 
FROM (`tbl_user`) 
JOIN `tbl_usercategory` ON `tbl_usercategory`.`usercategoryid`=`tbl_user`.`usercategoryid`

Inner Join

Program 1:

Used Two table Inner Join in codeigniter

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.type');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid','inner');
$query=$this->db->get();

SELECT `tbl_user`.`username`, `tbl_user`.`userid`, `tbl_usercategory`.`typee` 
FROM (`tbl_user`) 
INNER JOIN `tbl_usercategory` ON `tbl_usercategory`.`usercategoryid`=`tbl_user`.`usercategoryid`

Program 2:

Used Three table Inner Join in codeigniter

$this->db->select('username,amount, configdescription');
$this->db->from('tbl_user');
$this->db->join('tbl_usersubscription','tbl_usersubscription.userid=tbl_user.userid','inner');		
$this->db->join('tbl_subscription',
            'tbl_usersubscription.subscription_config_id=tbl_subscription.configid','inner');

		
SELECT `username`, `amount`, `configdescription` 
FROM (`tbl_user`) 
INNER JOIN `tbl_usersubscription` ON `tbl_usersubscription`.`userid`=`tbl_user`.`userid` 
INNER JOIN `tbl_subscription` ON `tbl_usersubscription`.`subscription_config_id`=`tbl_subscription`.`configid`

Left Join

Used Two table Left Join in codeigniter

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.type');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid','Left');
$query=$this->db->get();

SELECT `tbl_user`.`username`, `tbl_user`.`userid`, `tbl_usercategory`.`typee` 
FROM (`tbl_user`) 
Left JOIN `tbl_usercategory` ON `tbl_usercategory`.`usercategoryid`=`tbl_user`.`usercategoryid`

Right Join

Used Two table Right Join in codeigniter

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.type');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid','Right');
$query=$this->db->get();

SELECT `tbl_user`.`username`, `tbl_user`.`userid`, `tbl_usercategory`.`typee` 
FROM (`tbl_user`) 
Right JOIN `tbl_usercategory` ON `tbl_usercategory`.`usercategoryid`=`tbl_user`.`usercategoryid`