多对多自引用选择sql查询

I have a simple table (MySQL) set up for relationships between two users.

我有一个简单的表(MySQL)，用于设置两个用户之间的关系。

 [User1Id] [User2Id]
  10        15
  14        10
  10        13

But, I can't figure out how to do a SELECT in such a way that I can grab the opposite UserId of the user being requested. So, if I wanted to get all relations for the user with an ID of 10, it would grab the user IDs 15, 14, and 13, and ideally join the users table with those IDs to get those users usernames. My current try is less than ideal:

但是，我不知道如何执行SELECT才能获取被请求用户的相反用户id。因此，如果我想为ID为10的用户获取所有关系，它将获取用户ID 15、14和13，并且理想地将用户表与这些ID连接起来以获取这些用户名。我目前的尝试并不理想:

 SELECT u1.username AS U1Username, 
        u2.username AS U2Username, 
        u1.userId AS U1UserId, 
        u2.userId AS U2UserId 
 FROM buddies b 
 LEFT JOIN users u1 on u1.userId=b.user2Id 
 LEFT JOIN users u2 on u2.userId=b.user1Id 
 WHERE b.user1Id=:1 OR b.user2Id=:1

Then that gets filtered and rearranged in code. Is there a way that I could do one SQL query to grab everything that I need?

然后对其进行过滤，并在代码中重新排列。是否有一种方法可以执行一个SQL查询来获取我需要的所有内容?

4 个解决方案

#1

Use:

使用:

SELECT u.userid,
       u.username
  FROM USERS u
 WHERE EXISTS(SELECT NULL
                FROM BUDDIES b
               WHERE u.userid = b.user1id
                 AND b.user2id = 10)
    OR EXISTS(SELECT NULL
                FROM BUDDIES b
               WHERE u.userid = b.user2id
                 AND b.user1id = 10)

...or:

或:

SELECT a.user1id,
       u.username
  FROM BUDDIES a
  JOIN USERS u ON u.userid = a.user1id
 WHERE a.user2id = 10
UNION 
SELECT b.user2id,
       u.username
  FROM BUDDIES b
  JOIN USERS u ON u.userid = b.user2id
 WHERE b.user1id = 10

UNION will remove duplicates; UNION ALL will not remove duplicates and be faster for it.

联盟将消除重复的数据;UNION ALL不会删除副本，而且会更快。

#2

Might be able to be better optimized with, but I think this might work:

也许可以更好地优化，但我认为这可能行得通:

SELECT username, userId FROM users 
WHERE 
    userId IN (SELECT user1Id FROM buddies WHERE user2Id={ID})
  OR
    userId IN (SELECT user2Id FROM buddies WHERE user1Id={ID})

#3

I came up with this solution:

我想到了这个解决办法:

SELECT users.userId, users.username FROM users WHERE userId IN (
    SELECT CASE
        WHEN user2id = 10 THEN user1id
        WHEN user1id = 10 THEN user2id
    END AS id 
    FROM buddies WHERE (
        (buddies.User1Id = 10 AND buddies.User2Id != 10) OR 
        (buddies.User2Id = 10 AND buddies.user1Id != 10)
    )
)

#4

The union is the easiest solution to read, but because you asked for a non-union answer. It will probably be slower than UNION ALL, because of all the CASEs, and if you have someone that is Buddy with themselves, you will get two records.

union是最容易阅读的解决方案，但因为您需要非union答案。它可能会比UNION ALL慢，因为所有的情况，如果你有一个和自己是朋友的人，你会得到两个记录。

 SELECT 
    Case When u1.userid = :1 Then u1.username Else u2.username End AS LookForUser, 
    Case When u1.userid = :1 Then u2.username Else u1.username End AS BuddyName, 
    Case When u1.userid = :1 Then u1.userid Else u2.userid End AS LookForUserID, 
    Case When u1.userid = :1 Then u2.userid Else u1.userid End AS BuddyID, 
 FROM buddies b 
 LEFT JOIN users u1 on u1.userId=b.user2Id 
 LEFT JOIN users u2 on u2.userId=b.user1Id 
 WHERE b.user1Id=:1 OR b.user2Id=:1

#1