MySQL multiple counts and multiple joins

Question

I'm making a message board and am trying to construct a query. My database structure is as follows:

Table name: table row, table row

categories: id, name
topics: id, categoryid
posts: id, topicid

On the homepage I'd like to list out each category and the number of topics and number of posts in each category. I've looked at some queries that use multiple joins but I'm having trouble applying the syntax to my situation.

Here's what I've come up with:

SELECT `categories`.`id`, `categories`.`name`,  
    SELECT COUNT(DISTINCT `topics`.`id`) 
    FROM `topics`
    WHERE `topics`.`categoryid` = `categories`.`id`
    AS `numtopics`,
    SELECT COUNT(DISTINCT `posts`.`id`)
    FROM `posts`
    WHERE `posts`topicid` = `topics`.`id` 
    AS `numposts`
FROM `categories` 
JOIN `topics` ON `categories`.`id` = `topics`.`categoryid` 
JOIN `posts` ON `topics`.`id` = `posts`.`topicid`
;

I start to get confused when I try to count the number of posts based on the topicid and then join that with the table created by the first join. Is it possible to do what I'm doing? If so, am I going about it correctly?

Wrikken · Accepted Answer · 2013-05-01 21:18:52Z

2

SELECT `categories`.`id`, `categories`.`name`,
   COUNT(DISTINCT topics.id) as topics,
   COUNT(DISTINCT posts.id) as posts
FROM `categories` 
LEFT JOIN `topics` ON `categories`.`id` = `topics`.`categoryid` 
LEFT JOIN `posts` ON `topics`.`id` = `posts`.`topicid`
GROUP BY categories.id

... but getting all the posts & topics involved may be heavy on the server. I'd cache it somethere for a little while.

answered May 1, 2013 at 21:18

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2200770 Over a year ago

Thank you! I'll keep that in mind, though I don't expect much activity at all for now.

Collectives™ on Stack Overflow

MySQL multiple counts and multiple joins

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related