[El Forum]

[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 .......

[El Forum]

[eluser]Bigil Michael[/eluser]
can any one help me.....

[El Forum]

[eluser]Bigil Michael[/eluser]
bump....