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