Skip navigation links

MySQL Forums :: Newbie :: Joining one table twice -> getting value^2


Advanced Search

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
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.