sql sub query problem

Question

    +--------------+--------------+------+-----+-------------------+----------------
+
| Field        | Type         | Null | Key | Default           | Extra
|
+--------------+--------------+------+-----+-------------------+----------------
+
| mag_id       | int(11)      | NO   | PRI | NULL              | auto_increment
|
| cat_id       | int(11)      | NO   |     | NULL              |
|
| mag_cat_id   | int(11)      | NO   |     | NULL              |
|
| name         | varchar(512) | NO   |     | NULL              |
|
| publish_type | varchar(256) | NO   |     | NULL              |
|
| chief        | varchar(256) | NO   |     | NULL              |
|
| tel          | varchar(256) | NO   |     | NULL              |
|
| fax          | varchar(256) | NO   |     | NULL              |
|
| website      | varchar(256) | NO   |     | NULL              |
|
| email        | varchar(256) | NO   |     | NULL              |
|
| issue_number | varchar(256) | NO   |     | NULL              |
|
| keyword      | varchar(512) | NO   |     | NULL              |
|
| index        | tinyint(1)   | NO   |     | 0                 |
|
| view_num     | int(11)      | NO   |     | 0                 |
|
| download     | int(11)      | NO   |     | 0                 |
|
| act_date     | timestamp    | NO   |     | CURRENT_TIMESTAMP |
|
+--------------+--------------+------+-----+-------------------+----------------

and category table

+----------+--------------+------+-----+-------------------+----------------+
| Field    | Type         | Null | Key | Default           | Extra          |
+----------+--------------+------+-----+-------------------+----------------+
| cat_id   | int(11)      | NO   | PRI | NULL              | auto_increment |
| name     | varchar(256) | NO   |     | NULL              |                |
| act_date | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+----------+--------------+------+-----+-------------------+----------------+

i want to create a menu of category like this : movie(2) politics (19)

the number comes from number of magazine inside that category , but i don't know how to query database to create this . i loop throught category table and inside that loop i send each category id to magazine table and fetch number from magazine table but i think it's not correct way to do this . here is my code .

<?php 
$category = $this->db->get('category')->result();
foreach($category as $c):?>
<li>
<?=anchor('main/get/'.$c->cat_id ,'<img src="'.base_url().'images/bullet.gif" border="0"/>&nbsp;&nbsp;'.$c->name .' ( '.$this->category_model->get_mag_in_category($c->cat_id) .')');?>
<!-- Show magazine in each category -->
</li> 
<?php endforeach;?>

i think i need to do this with sub query .

Answer 1

This will return categories even if they have no associated magazines:

select c.name, count(*) as Count
from category c
left outer join magazine m on c.cat_id = m.cat_id
group by c.name

If you only want categories with magazines, do this:

select c.name, count(*) as Count
from category c
inner join magazine m on c.cat_id = m.cat_id
group by c.name