Many to many relationship problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Many to many relationship problem

Author

Topic

dannylister
Starting Member

9 Posts

Posted - 2007-10-24 : 18:57:41

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.

http://www.dannylister.co.uk/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-24 : 19:10:14

[code]
SELECT Id, Name, (Select count(*) FROM PostsCategory WHERE CategoryID = C.CategoryID) As TotalPosts
FROM Categories C
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dannylister
Starting Member

9 Posts

Posted - 2007-10-25 : 04:54:57

Thanks for your reply. It worked perfectly. I seemed to have over complicated things when trying to solve the problem. What about returning the same set of results but where the total posts is greater then 0?

I presumed I would of just added a where clause to the main body of the statement (see below), but it fails to recognise the 'TotalPosts' alias

WHERE TotalPosts > 0

Cheers for your help.

Danny.

http://www.dannylister.co.uk/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-25 : 05:00:20

[code]SELECT c.ID, c.Name, t.ToalPosts
FROM Categories C
INNER JOIN
(
SELECT CategoryID, TotalPosts = COUNT(*)
FROM PostsCategory
GROUP BY CategoryID
HAVING COUNT(*) > 0
) t
ON c.ID = t.CategoryID[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-25 : 05:05:45

quote:
I presumed I would of just added a where clause to the main body of the statement (see below), but it fails to recognise the 'TotalPosts' alias

WHERE TotalPosts > 0

You can't reference an column alias in the where clause.
One workaround is use a derived table

select *
from
(
    SELECT Id, Name, (Select count(*) FROM PostsCategory WHERE CategoryID = C.CategoryID) As TotalPosts
    FROM Categories C
) a
where TotalPosts > 0

Or write the query using INNER JOIN as in my earlier post.

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 05:08:14

Simpler?

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

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-25 : 05:18:28

Yes yes yes. What was i thinking