CODEWITHSUNDEEP
mysqlsqldatabase
Sohrab Hejazi
Sohrab Hejazi

Reputation: 1511

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.

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 id and names 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.

SELECT DISTINCT
users.ID userid
users.name name
FROM users
INNER JOIN friends
ON users.id = friends.fromid OR users.id = friends.toid
WHERE (friends.fromid = 1 OR friends.toid = 1) AND users.ID != 1

Upvotes: 1

Views: 1220

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

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

Check Fiddle Demo

INSERT INTO friends
    (`ID`, `toid`, `fromid`)
VALUES
    (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}

QUERY

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)
HAVING COUNT(*) = 2

Upvotes: 5

Related Questions