Help with query, please

[Julius]

Hello,

 

I have this query:

SELECT f.forum_name, f.forum_id, t2.topics, p2.posts, users.username
FROM forums f
LEFT JOIN (
        
SELECT t.topic_id, t.forum_id, count( t.topic_id ) AS topics
FROM topics t
GROUP BY t.forum_id
)t2 ON t2.forum_id = f.forum_id
LEFT JOIN (
        
SELECT p.post_id, count( p.post_id ) AS posts, p.topic_id
FROM posts p
GROUP BY p.topic_id
)p2 ON p2.topic_id = t2.topic_id
LEFT JOIN forum_moderators ON forum_moderators.forum_id = f.forum_id
LEFT JOIN group_user ON group_user.group_id = forum_moderators.group_id
LEFT JOIN users ON users.user_id = group_user.user_id
GROUP BY f.forum_position, group_user.group_id, users.username

the problem here is that query returns bad count of posts. I tried to remove one subquery, then the other one, then both, then play with group by, but that was useless. Can someone help me with this?

I removed few things that are not priority, and one subquery:

SELECT f.forum_name, count( topics.topic_id ) AS topics, p2.posts
FROM forums f
LEFT JOIN topics ON topics.forum_id = f.forum_id
LEFT JOIN (

SELECT p.post_id, count( p.post_id ) AS posts, p.topic_id
FROM posts p
GROUP BY p.topic_id
)p2 ON p2.topic_id = topics.topic_id
GROUP BY f.forum_position

To be clear: there are two topics and 18 posts in one of the forums (in one topic 16, other one 2 posts), query counts that that forum has 2 topics (correct) and 16 posts(incorrect). How do I fix this?

[fenway]

Perhaps with a COUNT DISTINCT.