sql子查询问题

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 |
|
+--------------+--------------+------+-----+-------------------+----------------

和类别表

+----------+--------------+------+-----+-------------------+----------------+
| 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 |                |
+----------+--------------+------+-----+-------------------+----------------+

我想创建一个这样的类别菜单:电影(2)政治 (19)

该数字来自该类别中的杂志数量，但我不知道如何查询数据库来创建它。我循环遍历类别表并在该循环内将每个类别 ID 发送到杂志表并从杂志表中获取编号，但我认为这不是正确的方法。这是我的代码。

<?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;?>

我想我需要用子查询来做这件事。

Answer 1

这将返回类别，即使它们没有关联的杂志:

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

如果您只想要杂志类别，请执行以下操作:

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