MySQL表连接

Question

I have a table which links the userID and the friendID (which is another user) to say they are friends. If User 1 adds User 24 as their friend, the first row in the below table will happen If User 10 adds User 1 as their friend, the second row in the below table will happen

From there, even if User 1 is on either side, they are friends with the adjacent ID.

FRIENDS TABLE:

userID | friendID
-----------------
   1   |    24
   10  |    1

What I need to do now is: If User 1 is logged in, I need to display the people they are friends with. This query I have wrote:

SELECT DISTINCT (Users.username), friends.userID, friends.friendID FROM Users, friends WHERE Users.userID IN(SELECT userID FROM friends WHERE Users.userID = friends.userID) OR Users.userID IN(SELECT DISTINCT(userID) FROM friends WHERE Users.userID = friends.friendID)

Will bring back:

username | userID | friendID
-----------------------------
   name1 |   10   |    1
   name1 |   1    |    24
   name2 |   10   |    1
   name2 |   1    |    24

name1 = ID 1 name2 = ID 10

Is there a way where I can retrieve the logged in Users'friends (e.g. if ID #1 is logged in) whether they appear in the userID or friendID columns. and then, in this case, retrieve ID 24 and 10 because they are linked with ID #1, and then when it comes to displaying, eliminate ID # 1 from the list because it will bring up that they are friends with themselves.

Hope this makes sense and thank you in advance!

Answer 1

Here's an easy way to do it with UNION. This also takes care of duplicates:

(SELECT userID
FROM friends
WHERE friendID = 1)
UNION
(SELECT friendID
FROM friends
WHERE userID = 1)

If you want to return usernames too, you can do your JOINs inside the subqueries:

(SELECT f.userID, u.username
FROM friends f
JOIN users u
  ON u.userID = f.userID
WHERE f.friendID = 1)
UNION
(SELECT f.friendID, u.username
FROM friends f
JOIN users u
  ON u.userID = f.friendID
WHERE f.userID = 1)