SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
-
Mar 9, 2002, 18:16 #1
- Join Date
- Sep 2001
- Location
- Mountain View, CA
- Posts
- 28
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Need help with left join query ...
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!
-
Mar 12, 2002, 23:26 #2
- Join Date
- Feb 2002
- Location
- Atlanta, GA
- Posts
- 342
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Mar 14, 2002, 18:50 #3
- Join Date
- Sep 2001
- Location
- Mountain View, CA
- Posts
- 28
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.John
AdBizInfo - Online Advertising Resource
Top10Links - Directory Of Top Sites
MovieRumors - Movie Message Board
Bookmarks