Need help with left join query ...

[JohnKelly]

Hi, I have two left join queries that work fine separately but when combined one of them does not return the correct count.

The first query outputs the proper number of posts and works fine ...

SELECT f_forums.fid, count(f_posts.postid) as nposts FROM f_forums LEFT JOIN f_posts ON f_posts.fid = f_forums.fid WHERE f_forums.cid = 11 GROUP BY f_forums.fid

The second query outputs the proper number of replies and works fine ...

SELECT f_forums.fid, count(f_replies.replyid) as nreplies FROM f_forums LEFT JOIN f_replies ON f_replies.fid = f_forums.fid WHERE f_forums.cid = 11 GROUP BY f_forums.fid

But when I try to combine the above two queries into the one below the number of posts is still correct but the number of replies is wrong (the number of replies outputted is the same as the number of posts which it should not be)

SELECT f_forums.fid, count(f_posts.postid) as nposts, count(f_replies.replyid) as nreplies FROM f_forums LEFT JOIN f_posts ON f_posts.fid = f_forums.fid LEFT JOIN f_replies ON f_replies.fid = f_forums.fid WHERE f_forums.cid = 11 GROUP BY f_forums.fid

Any idea how I can combine the first two queries into one and get the correct number of replies? I am using MySQL. Thanks!

[phpPete]

Can you post the structure of the tables involved?
I had a similar problem, but my joins were fine, it was the DB design that needed attention.

[JohnKelly]

Hi, thanks for the reply. Changing it to ...

count(DISTINCT f_posts.postid) as nposts, count(DISTINCT f_replies.replyid)

... seemed to solve the problem. I am using MySQL.