MySQL Rating system (calculating average from two tables)

Question

I have two tables, videos and videos_ratings. The videos table has an int videoid field (and many others but those fields are not important I think) and many records. The videos_ratings table has 3 int fields: videoid, rating, rated_by which has many records (multiple records for each fields from the videos table) but not for all records from the videos table.

Currently I have the following mysql query:

  SELECT `videos`.*, avg(`videos_ratings`.`vote`) 
    FROM `videos`, `videos_ratings` 
   WHERE `videos_ratings`.`videoid` = `videos`.`videoid` 
GROUP BY `videos_ratings`.`videoid` 
ORDER BY RAND() LIMIT 0, 12

It selects all the records from table videos that have a rating in table video_ratings and calculates the average correctly. But what I need is to select all records from the videos table, no matter if there is a rating for that record or not. And if there aren't any records in the videos_ratings table for that particular videos record, the average function should show 0.

Hope someone could understand what I want... :)

Thanks!

Answer 1

Use:

   SELECT v.*,
          COALESCE(x.avg_vote, 0)
     FROM VIDEOS v
LEFT JOIN (SELECT vr.videoid,
                  AVG(vr.vote) AS avg_vote
             FROM VIDEO_RATINGS vr
         GROUP BY vr.videoid) x ON x.videoid = v.videoid
 ORDER BY RAND()
    LIMIT 12

Be aware that ORDER BY RAND() does not scale well - see this question for better alternatives.

Answer 2

You need to do a LEFT JOIN to include all records from the videos table, even if there's no rating.

You can then put an if() in your select to turn a NULL rating to 0.