Left Join Help

[ShoeLace1291]

I am building a system with PHP and mysql that manages contests.  I have tables for contests, contest submissions, and contest votes.  I'm trying to get the top 10 records from the current contest for the number of votes a submission has and order the query results based on the number of rows in contest votes where the submission id in the votes table equals the submission id in the submissions table.  Here is the query I have.

 

                                SELECT sub.id, sub.title, sub.author_id, sub.attachment_id,
				vote.id, vote.author_id
			FROM contest_submissions AS sub
			LEFT JOIN contest_votes AS vote ON (vote.submission_id=sub.id)
			WHERE sub.contest_id = ".$contest['id']."
			ORDER BY COUNT(vote) DESC
			LIMIT 10

 

I'm not sure how to order the results because the error I'm getting says unknown column vote in where clause.  I'm guessing that's because it's trying to order by a column called vote when i just want to order it by the number of rows.  Anyone have any ideas?

[gristoi]

ORDER BY COUNT(vote.id) DESC

presuming that your id column is your primary key