Multiple Join Queries in CodeIgniter

in Joins , We have five types of queries to join data in codeigniter

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`