CODEWITHSUNDEEP
phpmysqlsqlcodeigniterblogs
Nathan Stanford II
Nathan Stanford II

Reputation: 617

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

blgcategories
==========
id
name
*Other schema unimportant

Upvotes: 1

Views: 576

Answers (3)

Quassnoi
Quassnoi

Reputation: 425391

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.

Upvotes: 0

GordyD
GordyD

Reputation: 5103

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

Upvotes: 3

Finbarr
Finbarr

Reputation: 32126

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;

Upvotes: 1

Related Questions