SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2001
    Location
    Mountain View, CA
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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!

  2. #2
    SitePoint Addict
    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.


  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2001
    Location
    Mountain View, CA
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •