multi-functions on multi-tables??

[wouble]

Hi all of you,
I wanna write a SELECT that pulls all data from one table, and performs a COUNT() on the other two tables. I can get everything to work fine with the first table and either one of the other two, but on putting two COUNT functions in the same query I get into trouble.

This works great

SELECT mt_forums.*,
COUNT(mt_forum_topics.topic_id) AS forum_topics
FROM mt_forums
JOIN mt_forum_topics ON mt_forums.forum_id=mt_forum_topics.forum_id
GROUP BY mt_forums.forum_id

and of course this works as well

SELECT mt_forums.*,
COUNT(mt_forum_posts.post_id) AS forum_posts
FROM mt_forums
INNER JOIN mt_forum_posts ON mt_forums.forum_id=mt_forum_posts.forum_id
GROUP BY mt_forum_posts.forum_id

but this one seems to multiply the two counts, and return the same value for each alias

SELECT mt_forums.*,
COUNT(mt_forum_topics.topic_id) AS -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[gilbert]

Yes, SQL aggregates multiply across multiple joins. So, try to use temp tables or subqueries, such as:

SELECT
f.*,
(SELECT COUNT(t.topic_id) FROM mt_forum_topics t WHERE t.forum_id=f.forum_id),
(SELECT COUNT(f.post_id) FROM mt_forum_posts f WHERE p.forum_id=f.forum_id),
FROM mt_forums f; -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --