[eluser]Bigil Michael[/eluser]
now I am creating SRO by grouping these areas. So created 2 more tables like this
Code:
sr_id sr_sro
1 SRO1
2 SRO2
for each SRO more than one area will present . So i created a new Table like this
lsa_id sr_id ar_id
1 1 1
2 1 2
3 1 3
4 2 1
Now I want to show the count of Area and locations inside that area
Code:
Result like this
name area location
SRO1 3 4
SRO2 1 0
I wrote the query like this
Code:
$this->db->order_by("SR.sr_id", "desc");
$this->db->select('SR.*, COUNT(P.ar_id) AS place_total,COUNT(LSA.ar_id) AS area_total, AR.ar_area');
$this->db->from('loc_sro AS SR');
$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('LSA.sr_id');
$result_blog = $this->db->get();
return $result_blog->result_array();
But it doesn't print the correct count.
can anybody help me to solve this issue......