tags:

views:

40

answers:

2

I'm having some trouble getting the number of posts and topics for each of the forums on the database. I can get these values doing 2 queries but I wonder if it is possible to do it with only one query.

This query gets the number of topics per forum:

select forums.forumId, forums.forumName, count(*) as Topics FROM Topics 
INNER JOIN forums ON forums.forumId = topics.forumID 
GROUP BY forums.forumId;

This query gets the number of posts per forum:

select forums.forumId, forums.forumName, count(*) as Posts FROM posts 
INNER JOIN topics ON topics.topicID = posts.topicId 
INNER JOIN forums ON forums.forumId = topics.forumID 
GROUP BY forums.forumId;

How do I get both post and topic count in just one query?

+2  A: 
SELECT  forums.forumId, forums.forumName,
        COUNT(DISTINCT topics.TopicID) AS Topics,
        COUNT(*) as Posts
FROM    forums
INNER JOIN
        topics
ON      topics.forumID = forums.forumId
INNER JOIN
        posts 
ON      posts.topicId  = topics.topicID
GROUP BY
        forums.forumId
Quassnoi
Thanks, it works! This query is actually faster than if it was only doing the count(*) part. Not by far but still... Any idea why using the second count would make things faster?
Uncle Paulie
I think it's just your results been cached. These queries in theory have same performance. However, you can post the query plans and I'll try to tell the difference if any.
Quassnoi
A: 
SELECT  forums.forumId, forums.forumName,
        COUNT(DISTINCT topics.TopicID) AS Topics,
        COUNT(posts.topicId) as Posts
FROM    forums
LEFT OUTER JOIN topics
ON      topics.forumID = forums.forumId
LEFT OUTER JOIN posts 
ON      posts.topicId  = topics.topicID
GROUP BY
        forums.forumId

You want to use left outer joins if you want to count forums with zero topics or posts

Wilhelm