我目前面临一个问题,无法链接3个独立的SQL表。以下是不同表格的相关部分。
Comments
commentID userID staffID comment comment_date
52 1037 0 sadsadsa 2016-01-12
54 0 1050 asda 2016-01-12
Users
userID first_name last_name
1036 Janet Ang
1037 glenn tan
Staffs
staffID staffName
1001 Ling Ling
1011 Lee Ming
1003 Joyce
1010 Titus
1008 Vivian
1005 Vincent
1006 Alex
1046 Glenn
1047 Glenn2
1048 glennjr2
1049 glenn3
1050 glenn4
我目前正在尝试检索用户和工作人员的评论,这三个表并不相通。"comments"表链接到"users"answers"staff"表,但"users"answers"staffs"表没有链接在一起。我正在使用此查询
SELECT comments.comment, users.first_name, users.last_name, staffs.staffName, comments.comment_date
FROM comments
INNER JOIN users
on comments.userID = users.userID
LEFT OUTER JOIN staffs
on comments.staffID = staffs.staffID
WHERE comments.videoID = $vid_id
有人能帮助我了解如何更改查询以解决问题吗?感谢您的帮助。
您可以将INNER JOIN用于(users,staffs)
和comments
表:
示例:
SELECT comments.comment, users.first_name, users.last_name, staffs.staffName, comments.comment_date
FROM comments
INNER JOIN users ON comments.userID = users.userID
INNER JOIN staffs ON comments.staffID = staffs.staffID
WHERE comments.videoID = $vid_id
之后,您将得到两个结果,一个用于users
,一个为staffs
。
如果您需要comments
可用或不可用的所有用户和工作人员,则可以使用具有相同查询的LEFT JOIN
。
您可以使用UNION操作:
示例:
( SELECT comments.comment, CONCAT(users.first_name, " ", users.last_name) as name, comments.comment_date, "User" as type
FROM comments, users
WHERE comments.userID = users.userID
AND comments.videoID = $vid_id )
UNION
( SELECT comments.comment, CONCAT(staffs.staffName) as name, comments.comment_date, "Staff" as type
FROM comments, staffs
WHERE comments.staffID = staffs.staffID
AND comments.videoID = $vid_id )