[eluser]Bigil Michael[/eluser]
I have 5 tables they are loc_place, loc_area, loc_sro, loc_sro_area, loc_region, loc_region_sro
Here a lot of places in each area
grouping area will create sro
grouping sros will create a region
My Table structures are given below
Code:
loc_place :
pl_id pl_place ar_id
loc_area :
ar_id ar_area sr_id reg_id ar_pincode
loc_sro :
sr_id sr_sro
loc_aro_area :
lsa_id sr_id ar_id
loc_region :
reg_id reg_region
loc_region_sro :
lrs_id reg_id sr_id
[code]
Now i Want show the count of each item like this
Region total sro under that region total area under that region total places under that region
I wrote the query like this
[code]
$this->db->select('R.*, COUNT(P.ar_id) AS place_total,COUNT(distinct LSA.ar_id) AS area_total, COUNT(distinct LRS.sr_id) AS sro_total, S.st_state, D.dst_district, AR.ar_area');
$this->db->from('loc_region AS R');
$this->db->join('loc_region_sro LRS', 'LRS.reg_id = R.reg_id', 'left');
$this->db->join('loc_sro SR', 'SR.sr_id = LRS.sr_id', 'left');
$this->db->join('loc_sro_area AS LSA', 'LSA.sr_id = SR.sr_id','left');
$this->db->join('loc_area AS AR','AR.ar_id = LSA.ar_id','left');
$this->db->join('loc_place AS P', 'P.ar_id = LSA.ar_id', 'left');
$this->db->group_by('LRS.reg_id');
$result_blog = $this->db->get();
return $result_blog->result_array();
It successfully prints count of sro and count of area but wrong count for place.
can any one help me to solve this issue .......