MySQL Forums
Forum List  »  Newbie

Joining one table twice -> getting value^2
Posted by: Jan Šeda
Date: April 25, 2012 10:44AM

I have two tables.
tbc_pics: id, name, path, gander...
tbc_votes: pic, date... (one vote = one row)
And now I need to get count of votes of every pic ORDERED by the count of votes in last month. I've done this:

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

it works but in allvotes I got count of votes^2. I have no idea why. Thank you for any help!

Options: ReplyQuote


Subject
Written By
Posted
Joining one table twice -> getting value^2
April 25, 2012 10:44AM


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.