Jump to content

Left Join Help


ShoeLace1291

Recommended Posts

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?

Link to comment
https://forums.phpfreaks.com/topic/238533-left-join-help/
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.