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

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