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 15is 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
|Joining one table twice -> getting value^2||Jan Šeda||04/25/2012 10:44AM|
|Re: Joining one table twice -> getting value^2||laptop alias||04/25/2012 10:53AM|
|Re: Joining one table twice -> getting value^2||Rick James||04/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.