显示帖子数量的类别

Question

I'm making a blog. I have 2 tables, one for posts and the other for categories.

I want to display the category name, category date and the number of posts in each category. I have problems to display the number of posts in each category. In posts table I have a column called cat_id which is equal to category.id

I have these 2 MySQL queries:

mysql_query("select Count(posts.id) as NumberOfPosts, cat_id from posts group by cat_id");  

And

mysql_query("select name, date from categories");

I don't know how to have combine these two queries into one query. I'm using PHP.

Answer 1

You could use a join:

SELECT name, date, NumberOfPosts
FROM   categories c
JOIN   (SELECT   cat_id, COUNT(*) AS NumberOfPosts
        FROM     posts
        GROUP BY cat_id) p ON c.id = p.cat_id

EDIT:
To include categories with no posts, you could use a left join instead of regular join. You just need to handle the nulls you'd get for NumberOfPosts, e.g., by using coalesce:

SELECT      name, date, COALESCE(NumberOfPosts, 0) AS NumberOfPosts
FROM        categories c
LEFT JOIN   (SELECT   cat_id, COUNT(*) AS NumberOfPosts
             FROM     posts
             GROUP BY cat_id) p ON c.id = p.cat_id