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!