Many-to-Many self-referencing select sql query

Question

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

 [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 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?

Answer 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.

Answer 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})

Answer 3

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.

 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

Answer 4

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)
    )
)