Hi Using mysql 3.23.54
I'm trying to join three tables. categories, topics, posts. I just want the categories to print out, with the number of topics in each category, and the number of posts in each topic. ex output: Category | Topics | Posts ---------------------------- Cat One | 3 | 15 .... .... I have this: SELECT forum_categories.id AS `id` , forum_categories.name AS `name` , forum_categories.createdby AS `createdby` , forum_categories.order AS `order` ,DATE_FORMAT( forum_categories.created, "%m/%d/%y %l:%i %p" ) AS `created` , COUNT( forum_topics.id ) AS `topics`, SUM(forum_posts.id) AS `posts` FROM forum_categories LEFT JOIN forum_topics ON forum_categories.id = forum_topics.category LEFT JOIN forum_posts ON forum_topics.id = forum_posts.topic WHERE forum_categories.domain = 000001 GROUP BY forum_categories.id ORDER BY forum_categories.order ASC Pulling from: 2 Categories. There are 3 topics under Category 1, and there are two Posts for Topic 1, 1 for Topic 2, and 0 for Topic 3. 1 topic under Category 2, and one Post under that Topic. Gives me: +--------+---------------------+---------------+-------+-------------------+ --------+-------+ | id | name | createdby | order | created | topics | posts | +--------+---------------------+---------------+-------+-------------------+ --------+-------+ | 000029 | Test Category One | Chris Edwards | 0 | 11/06/03 11:00 | 4 | 6 | | 000031 | Test Category Three | Chris Edwards | 1 | 11/06/03 11:00 | 1 | 0 | +--------+---------------------+---------------+-------+-------------------+ --------+-------+ My problem is getting the grouping to work or something. The query ends up multiplying the topics and posts to create the post count. I cannot figure out how to prevent this. I need the posts to say 3 and 0, not 6 and 0. I hope this is enough information. Thanks. -- Chris Edwards Web Application Developer Outer Banks Internet, Inc. 252-441-6698 [EMAIL PROTECTED] http://www.OuterBanksInternet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
