我正在尝试连接3个mysql表;朋友，用户和评论。
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
因此，我试图做的是选择所有用户朋友的评论。所以我想输出的是你朋友的评论：前任：
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
它会得到所有朋友的评论，但不会得到他的评论。这是我的代码：
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'
它确实有效，但我没有得到评论者的名字，而是得到了他们所有人的埃德温·莱昂
你想将用户表连接到 friendId 而不是 userid：
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
试试这个：
Select friendId, comment, commentdate, firstname, lastname
from
friends inner join comments on (friends.friendId=comenter)
inner join users on (users.id=friends.friendId)
where friends.userid=?
试试这个（我还没有测试过）
SELECT friends.friendID, u2.firstName, u2.lastName, comments.comment, comments.commentDate
FROM users AS u
JOIN friends ON friends.userID = u.id
JOIN comments ON comments.commenter = friends.friendID
JOIN users AS u2 ON u2.id = friends.friendID
WHERE u.id = '12' AND comments.commenter != '12'