Probably pretty simple for experienced mysql-ers, but I'm at a loss...
Say I have two tables: Movies & Reviews.
Movies has a RecNum field, a Title field & other info
Reviews has a MovieNum field (identifying which Movie RecNum it's for), a Rating field (1-5 if rated, 0 if not), and other data. A Movie may have any number of Reviews.
What I want to do is to select all of the Movies and sort them (ASC or DESC) based on the average of all Reviews for that particular movie where the Rating is not 0.
I believe there's a pretty easy way of doing this, but I can't figure it out...JOIN? GROUP? Something else? I dunno...
Any assistance appreciated...