Using join and left join i joined more than 4 tables. Case 1: If the subscription type is institution i joined institution, country, state, product and subscribed product table, works well
Case 2:
if it is dealer joined dealer, client, country, state, product and subscribed product table.
Some dealers have client (compare country and state with client table). Some one didn't have client (compare country and state with dealer table).
I struck in this area
Below is my model code
$this->db->limit($limit, $start);
$this->db->distinct('iman_subscribed_products.subscription_id');
$this->db->select('*,country_name,state_name');
//$this->db->join('iman_subscribed_products', 'iman_despatch_details.product_id = iman_subscribed_products.product_id',right);
if($data['dealtype']=='Dealer'){
$this->db->join('iman_agent_details', 'iman_agent_details.agentid=iman_subscribed_products.dealer_id');
$this->db->join('iman_client_details', 'iman_client_details.agentid=iman_agent_details.agentid',left);
$this->db->join('iman_country', 'iman_country.id=iman_agent_details.agentcountry',left);
$this->db->join('iman_state', 'iman_state.id=iman_agent_details.agentstate',left);
}else{
$this->db->join('iman_subscriber_insitution_details', 'iman_subscriber_insitution_details.sub_institute_id=iman_subscribed_products.institution_id',left);
$this->db->join('iman_country', 'iman_country.id=iman_subscriber_insitution_details.sub_ins_country',left);
$this->db->join('iman_state', 'iman_state.id=iman_subscriber_insitution_details.sub_ins_state',left);
}
$this->db->join('iman_product', 'iman_product.productid=iman_subscribed_products.product_id',left);
$this->db->where('iman_subscribed_products.product_id',$data['prodname']);
if(!empty($startyear)){
if($data['dealtype']=='Dealer'){
if($val1[0]!=''){
$this->db->where_not_in('iman_client_details.clientid', $val1);
}
else{
$this->db->where('iman_subscribed_products.dealer_id', $data['dealerid']);
$this->db->where('iman_subscribed_products.dealer_id !=',0);
}
}else{
if($val1[0]!='')
$this->db->where_not_in('iman_subscriber_insitution_details.sub_institute_id', $val1);
$this->db->where('iman_subscriber_insitution_details.sub_institute_id !=',0);
}
$this->db->where('iman_subscribed_products.year',$iss1[1]);
$this->db->where('iman_subscribed_products.start_date <= date("'.$startdate.'")');
$this->db->where('iman_subscribed_products.end_date >= date("'.$enddate.'")');
}
$query = $this->db->get('iman_subscribed_products');
echo $sql = $this->db->last_query();
return $query->result();
After execution query look like this
SELECT DISTINCT *,
`country_name`,
`state_name`
FROM (`iman_subscribed_products`)
JOIN `iman_agent_details` ON `iman_agent_details`.`agentid`=`iman_subscribed_products`.`dealer_id`
LEFT JOIN `iman_client_details` ON `iman_client_details`.`agentid`=`iman_agent_details`.`agentid`
LEFT JOIN `iman_country` ON `iman_country`.`id`=`iman_agent_details`.`agentcountry`
LEFT JOIN `iman_state` ON `iman_state`.`id`=`iman_agent_details`.`agentstate`
LEFT JOIN `iman_product` ON `iman_product`.`productid`=`iman_subscribed_products`.`product_id`
WHERE `iman_subscribed_products`.`product_id` = '3'
AND `iman_subscribed_products`.`dealer_id` = '23'
AND `iman_subscribed_products`.`dealer_id` != 0
AND `iman_subscribed_products`.`year` = '2016'
AND `iman_subscribed_products`.`start_date` <= date("2016-4-01")
AND `iman_subscribed_products`.`end_date` >= date("2016-6-30") LIMIT 5
Kindly help me