[SQL] Many to many relationship problem

[dannylister]

Hi all,

I've got a slight problem with a stored procedure I'm trying to create. I'll talk you through the story so far. I currently have the following tables within my database:


Categories
------------------
ID
Name


Posts
------------------
ID
Date
Title


And a many to many relationship table where each ID below links to the respective table above:


PostsCategory
------------------
PostID
CategoryID


What I want to do is to retrieve a list of categories and the total amount of posts in each category. So far, my SQL statement for doing this is:


SELECT DISTINCT
Categories.Name
COUNT(PostsCategory.CategoryID) AS TotalPosts
FROM
Category
INNER JOIN
PostsCategory ON Categories.ID = PostsCategory.CategoryID
GROUP BY
Category.Name
ORDER BY
Category.Name


This works perfectly except for when a category has no posts. Ie: there's no links in the PostsCategory table. I would like the statement to retreive all categories no matter how many posts have been made. For example, the above SQL statement returns the following:


Name, TotalPosts
------------------------------------
Category 1, 10
Category 2, 5
Category 4, 47


But I want it to be able to return the below example:


Name, TotalPosts
------------------------------------
Category 1, 10
Category 2, 5
Category 3, 0
Category 4, 47
Category 5, 0
Category 6, 0


If anyone could possibly help me then that would be great Sorry for the poor formatting. Tabs won't display

Cheers, from Danny.

 

[dannylister]

Cheers for your help guys I've figured it out from the above posts. Just needed a left join instead of an inner join.