代码之家 › 专栏 › 技术社区 › halocursed

当外键不返回任何内容时,我如何在MySQL中选择记录?

left-join mysql database

0

halocursed · 技术社区 · 16 年前

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;

video_ratings , game_data ,视频。..

)因为 AND videos.id = video_ratings.video_id

AVG(video_ratings.rating) 视频_广告 AVG(视频评级)

谢谢

1 回复 | 直到 16 年前

1

3

cletus 16 年前

是的,只需使用左外内连接而不是内连接(这就是你正在做的)。我还建议您使用JOIN语法。这要清楚得多:

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