[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.

 

[stx666]

What you would require is an left outer join
it looks like your using sql server?

It should look something like this.

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

The outer join takes the using either left or right to denote the primary table. It wil then select all the relevent rows from that table, and if there isn't a corresponding link against the child table a null is returned.

 

[RobH]

And counting a null returns '0' I am guessing?

 

[stx666]

yes, a null won't be counted.

 

[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.