How can I select records in MySQL when a foreign key doesn't return anything?

Question

SELECT videos.id, videos.game_id, videos.xbox360, videos.ps3, videos.pc,
  videos.wii, videos.other, videos.thumbnail, videos.vid_info, videos.sdvid,
  videos.hdvid, UNIX_TIMESTAMP( videos.date_added ) , game_data.name, 
  AVG( video_ratings.rating )
FROM videos, game_data, video_ratings
WHERE videos.game_id = game_data.id
  AND videos.id = video_ratings.video_id
GROUP BY videos.id, video_ratings.video_id
ORDER BY videos.date_added DESC LIMIT 10;

I am running this query to extract data from three tables video_ratings, game_data, videos...

Now the problem I'm facing is the result only shows the videos that have been rated (or are in table video_ratings) because of AND videos.id = video_ratings.video_id...

Is there any way that I can select the data for all videos and the result shows AVG(video_ratings.rating) as null if ratings for those videos is not present in the video_ratings table (or say none of the videos have been rated so the result must show 10 rows with AVG(video_ratings.rating) column as null ) ...

Thanks

Answer 1

Yeah, just use a left outer inner instead of an inner join (which is what you're doing). I'd also suggest you use the JOIN syntax instead. It's far clearer:

SELECT v.id, v.game_id, v.xbox360, v.ps3, v.pc, v.wii,
v.other, v.thumbnail, v.vid_info, v.sdvid, v.hdvid,
UNIX_TIMESTAMP(v.date_added), gd.name, AVG(vg.rating)
FROM videos v
LEFT JOIN game_data gd ON gd.id = v.game_id
LEFT JOIN video_ratings vr ON v.id = vr.video_id
GROUP BY videos.id, video_ratings.video_id
ORDER BY videos.date_added DESC LIMIT 10