mysql - 如何编写mysql查询来实现以下类型的连接(Join based on value of a column)

Question

表用户:

id----email 
1-----s@s.com 
2-----p@p.com 

表 user_buddies

id-----user_id----buddy_id----status enum('accepted','pending') 
1------1----------2-----------pending 

假设我正在查询用户 2，然后我想获取用户 2 的电子邮件。如果我查询用户 1，那么我想获取用户 1 的电子邮件。

下面的查询总是返回空行。

SELECT
    users.email,
    ub1.*, ub2.*
FROM
    users
JOIN user_buddies ub1 ON users.id = ub1.user_id
JOIN user_buddies ub2 ON users.id = ub2.buddy_id
WHERE
    users.id = 1;

谁能帮我解决这个问题？谢谢。

预期结果:

email-----user_id-----buddy_id-----status
s@s.com --1-----------2------------pending (If queryed on user 2)
p@p.com---1-----------2------------pending (If queried on user 1)

上下文:

当 user1 向 user2 发送请求时 只有一行被添加 到 user_buddies 表中 状态=待定。因此，user1 和 user2 好友列表都是使用同一行创建的。因此，如果查询 user2 所需的电子邮件是 user2 的。

Answer 1

您可以检查以下查询:

SELECT
    users.email,
    ub1.user_id,
    ub1.buddy_id,
    ub1.`status`
FROM
    users
JOIN user_buddies ub1 ON users.id = ub1.user_id OR users.id = ub1.buddy_id
WHERE   users.id = 2;

SQL FIDDLE DEMO

结果:

email      user_id     buddy_id     status
p@p.com       1           2         pending (if queried on users.id = 2)
s@s.com       1           2         pending (if queried on users.id = 1)

编辑:

SELECT
    (
        SELECT
            u.email
        FROM users u
        WHERE   u.id =
        IF (
            users.id = ub1.user_id,
            ub1.buddy_id,
            ub1.user_id
        )
    ) AS buddyEmail,
    ub1.user_id,
    ub1.buddy_id,
    ub1.`status`
FROM
    users
INNER JOIN user_buddies ub1 ON users.id = ub1.user_id
OR users.id = ub1.buddy_id
WHERE   users.id = 2;

Check this fiddle of this new query