Sohrab Hejazi Sohrab Hejazi - 1 year ago 57
SQL Question

Select all ids for of the friends for a particular user

Need some assistance from SQL experts out there. Quick overview of the structure of my two tables that I'm looking to query from.

  • users table

    • id - (int) primary key

    • name - (string)

  • friends table

    • id - (int) primary key

    • toid - int

    • fromid - int

friends table holds the friendship relationship between users. The tricky part is that it holds two records per friendship.

So for example if we have three users in the database with id 1, 2, 3 and their names are Rob, John and Adam respectively.

If Rob and John are friends, the friends table has the following two entries:

id toid fromid

1   1   2

2   2   1

There are also situations where there is only one entry in the friends table. This happens when a request has been sent by one person but not excepted by the recipient yet.

What I am trying to accomplish is to select all ids for of the friends for a particular user. (Friends being the ones that have both entries in the friends table, not just one).

What I have tried so far gives me list of ids for both friends and users who have either sent or received a request for a particular user. Below is the closest I've gotten.


users.ID userid

FROM users

INNER JOIN friends

ON = friends.fromid OR = friends.toid

WHERE (friends.fromid = 1 OR friends.toid = 1) AND users.ID != 1

Answer Source

This will give you the list of ID with two way friendship with @userID.

Check Fiddle Demo

    (`ID`, `toid`, `fromid`)
    (1, 1, 2),
    (2, 2, 1),
    (3, 1, 3),
    (4, 3, 1),
    (5, 2, 3)
  • with @userID = 1 you get {2, 3}
  • with @userID = 2 you get {1} //2 sent to 3 but not receive it back
  • with @userID = 3 you get {1}


SELECT  CASE WHEN toid = @userID THEN fromid 
                                 ELSE toid 
        END as myFriendID
FROM friends
WHERE  toid = @userID OR fromid = @userID
GROUP BY LEAST(toid, fromid), GREATEST(toid, fromid)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download