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!
