MySQL Forums
Forum List  »  Newbie

Re: Joining one table twice -> getting value^2
Posted by: Rick James
Date: April 27, 2012 08:57AM

The problem in
SELECT  id, name, path, gender, COUNT( avotes.pic ) AS allvotes
    FROM  tbc_pics
    LEFT JOIN  tbc_votes AS avotes ON avotes.pic = tbc_pics.id
    LEFT JOIN  tbc_votes as tvotes ON ( tvotes.pic = tbc_pics.id
              AND  tvotes.date > ( UNIX_TIMESTAMP( ) - 2678400 ) )
    WHERE  ( STATUS =1
              OR  STATUS =2 )
    GROUP BY  tbc_pics.id
    ORDER BY  COUNT( tvotes.pic ) DESC
    LIMIT  15
is that you have one vote per row. You need one _total_ per row. So, let's compute totals in subqueries, then do the JOINs:
SELECT  id, name, path, gender,
        IFNULL(avotes.ct, 0) AS allvotes
    FROM  tbc_pics
    LEFT JOIN  
      ( SELECT  pic, COUNT(*) AS ct
            FROM  tbc_votes ) AS avotes ON avotes.pic = tbc_pics.id
    LEFT JOIN  
      ( SELECT  pic, COUNT(*) AS ct
            FROM  tbc_votes tvotes.date > UNIX_TIMESTAMP( ) - 30*24*60*60 ) as tvotes ON tvotes.pic = tbc_pics.id
    WHERE  STATUS IN (1, 2)
    GROUP BY  tbc_pics.id
    ORDER BY  tvotes.ct DESC
    LIMIT  15 

Options: ReplyQuote


Subject
Written By
Posted
Re: Joining one table twice -> getting value^2
April 27, 2012 08:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.