Category post count

I am building a blog with Codeigniter and MySQL. The question I have is this, I have a table with posts and one with categories. I also have a cross reference table with post_categories. What I am trying to do is get all the categories with their names and the number of posts they have under their name.

Example output would be: Hello World(1) Test(0) etc.

What I am having a hard time finding is a SQL query that will join the three tables and get me the counts, and I am also having a hard time wrapping my head around how to make that query.

Here is my table schema:

blgpost
====
id
*Other schema unimportant

blgpostcategories
=================
postid
categoryid

blg开发者_JAVA百科categories
==========
id
name
*Other schema unimportant

This should give you the output you want....

SELECT c.name, COUNT(p.id) FROM
blgcategories c 
INNER JOIN blgpostcategories pc ON c.id = pc.categoryid
INNER JOIN blgpost p ON pc.postid = p.id
GROUP BY c.id

You don't need to join the three tables - the blgpost table doesn't have any information in it that you need.

SELECT COUNT(*), blgcategories.name 
FROM blgcategories INNER JOIN blgpostcategories 
ON blgcategories.id=blgpostcategories.categoryid 
GROUP BY blgcategories.id;

SELECT  name, COUNT(pc.id)
FROM    blgcategories c
LEFT JOIN
        blgpostcategories pc
ON      pc.categoryid = c.id
GROUP BY
        c.id

Using LEFT JOIN will show 0 for empty categories (those without posts linked to them) rather than omitting them.