Select-sql query for many-to-many

Question

I have a simple table (MySQL) configured for a relationship between two users.

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

But I can’t figure out how to make SELECT so that I can capture the opposite UserId of the requested user. So, if I wanted to get all the relationships for the user with identifier 10, he would capture user IDs 15, 14 and 13 and ideally go into the user table with these identifiers to get these user names. My current attempt 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 it is filtered and reordered in the code. Is there a way that I could make one SQL query to capture everything I need?

Answer 1

:

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)

... :

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 ; UNION ALL .

Answer 2

, , :

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

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

Answer 4

Union is the easiest solution to read, but because you asked for a non-union response. It will probably be slower than UNION ALL due to all CASEs, and if you have someone who is Buddy with you, you will get two entries.

 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