query problem

[eluser]Bigil Michael[/eluser]
here i want to join two table loc_area and loc_place

Code:

loc_area like this

ar_id        ar_area
1               AR1
2               AR2
3               AR3


loc_place is like this

pl_id        pl_place        ar_id
1               PL1                2
2               PL2                2
3               PL3                2
4               PL4                3

i want the result like this

Code:

ar_area         total
AR1                 0
AR2                 3
AR3                 1

I wrote the query like this

Code:

$this->db->select('AR.*, COUNT(distinct P.ar_id)  AS total');
     $this->db->from('loc_area AS AR');    
     $this->db->join('loc_place AS P', 'P.ar_id = AR.ar_id', 'left');    
     $this->db->group_by('AR.ar_id');  
     $result_blog = $this->db->get();    
     return $result_blog->result();

But it prints the result only once.

can any one help me to solve this issue... thanks in advance

[eluser]Tpojka[/eluser]

Code:

return $result_blog->result_array();

[eluser]Bigil Michael[/eluser]
thanks..
problem solved

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