javascript - 需要在MySQL中查找返回某个结果的查询

Question

我有两个表，用户和关系。我需要找到所有向登录用户发送好友请求的用户。

User Table                        relationship Table
------------         ----------------------------------------------
id | username         user_one_id | user_two_id | status | action_user_id

这是我的两张 table 。 user_one_id 是发送请求的用户，而 user_two_id 是接收者。

我需要找到一个查询，该查询将返回登录用户收到的所有好友请求。

如果尝试以下操作，但它只返回登录用户的名称和向他们发送请求的用户的 ID。未返回发件人姓名。

 var loggedInUser = req.user[0];
 connection.query("
 SELECT User.id, User.username, relationship.user_one_id, 
 relationship.status 
 FROM User INNER JOIN relationship ON User.id=relationship.user_one_id 
 WHERE User.id = ?",
 [loggedInUser], function(err, results, fields) {}

任何帮助将不胜感激。

Answer 1

您需要在查询中使用两个 INNER JOIN 语句，一个用于 user_one_id，另一个用于 user_two_id:

SELECT userA.id, userA.username, userB.id, userB.username, status, action_user_id
FROM relationship
INNER JOIN User AS userA ON userA.id = relationship.user_one_id
INNER JOIN User AS userB ON userB.id = relationship.user_two_id
WHERE userA.id = ?

更新答案:

请参阅此处的链接了解更多详细信息:http://sqlfiddle.com/#!9/af7ce/5

SELECT userA.id AS userA_id, userA.username AS userA_username, userB.id AS userB_id, userB.username AS userB_username, status, action_user_id, userC.username AS action_user_username
FROM relationship
INNER JOIN User AS userA ON userA.id = relationship.user_one_id
INNER JOIN User AS userB ON userB.id = relationship.user_two_id
INNER JOIN User AS userC ON userC.id = relationship.action_user_id
WHERE (userA.id = 1 OR userB.id = 1) AND userC.id != 1