php - Mysql加入多于2个表

Question

我正在尝试连接 3 个 mysql 表； friend 、用户和评论。

users:
id  | firstName | lastName
--------------------------
12  | edwin     | leon
9   | oscar     | smith
1   | kasandra  | rios

friends:
userId   | friendID
----------------------
   12    | 9
   9     | 12
   12    | 1
   1     | 12

comments:
commenter | comment  | commentDate
----------------------------
 12       |  hey     | Oct-2
 1        | Hmmmmmm  | Nov-1
 9        | ok       | Nov-2
 9        | testing  | Nov-2
 1        | hello    | Dec-20    
 1        | help     | Dec-20

所以我要做的是选择用户 friend 的所有评论。所以我要输出的是你 friend 发表的评论:例如:

for edwin leon (id 12) it would output this
friendID    | comment  | commentDate | firstName | lastName
-----------------------------------------------------------
   1        | Help     |    Dec-20   | kasandra  | rios
   1        | Hello    |    Dec-20   | kasandra  | rios
   9        | testing  |    Nov-2    | oscar     | smith
   9        | ok       |    Nov-2    | oscar     | smith
   1        | Hmmmm    |    Nov-1    | kasandra  | rios

它会得到所有 friend 的评论，但他不会。这是我的代码:

SELECT friends.friendID, users.firstName, users.lastName, comments.comment, comments.commentDate  
FROM users
  JOIN friends ON friends.userID = users.id
  JOIN comments ON comments.commenter = friends.friendID 
 WHERE users.id = '12' AND comments.commenter != '12'

它确实有效，但我没有得到评论者的名字，而是得到了所有评论者的 edwin leon

Answer 1

您想将用户表连接到 friendId 而不是用户 ID:

SELECT friends.friendID, users.firstName, users.lastName, comments.comment, comments.commentDate  
FROM users
  JOIN friends ON friends.friendID = users.id
  JOIN comments ON comments.commenter = friends.friendID 
 WHERE friends.userID = '12' AND comments.commenter != '12'

在线查看它:sqlfiddle