SQL：3表内连接返回多个结果

Question

SELECT video.name, video.description, video_source.url, bitcast_user.username
FROM video
INNER JOIN bitcast_user ON video.account_id = bitcast_user.id
INNER JOIN video_source ON video_source.video_id = {$_GET['id']};

This returns results with incorrect video names, descriptions and associated accounts but correct sources. There is a one-to-many relationship between users and videos, and videos and sources.

Answer 1

You shouldn't do an INNER JOIN on a variable - that belongs in the WHERE clause. (Actually - I was even surprised that this worked at all.) I think you need a query like this:

SELECT video.name, video.description, video_source.url, bitcast_user.username
  FROM video
  INNER JOIN bitcast_user ON video.account_id = bitcast_user.id
  INNER JOIN video_source ON video_source.video_id = video.video_id
  WHERE video_source.video_id = {$_GET['id']};

(I'm not sure about video_source.video_id = video.video_id because I don't know how the column is named in the video table.)