CODEWITHSUNDEEP
mysql
geoffs3310
geoffs3310

Reputation: 14008

mysql query help to find mutual friends on social networking site

I have a database table called users with a primary key of user_id for each user.

I also have a table called friends with two fields, user_id and friend_user_id.

The user_id field is always the lowest of the two user_id's in order to avoid duplicate entries.

Say I have two users in mind, (lets say user id 1 and user id 4 although they could be anything).

How would I return all rows from the users table for users that are friends with user 1 and user 4 (i.e mutual friends)?

Upvotes: 0

Views: 2231

Answers (2)

Icarus
Icarus

Reputation: 63970

I will give you the recipe:

  1. Find all friends of user 1
  2. Find all friends of user 2
  3. Intersect them and the result will be the mutual friends.

Much like this:

enter image description here

UPDATE: Here's the query:

select f.friend_user_id  from friends f where f.friend_user_id in (
   select friend_user_id from friends where user_id=<id_of_user_a>)
and f.user_id=<id_of_user_b>

The ids returned by above query will be the id of all the users that are mutual friends of user_a and user_b. If you want to get all the details (name, etc) about those users, then do this:

   select f.friend_user_id,u.*  from friends f inner join users u 
   on u.user_id=f.friend_user_id
   where f.friend_user_id in (
   select friend_user_id from friends where user_id=<id_of_user_a>)
   and f.user_id=<id_of_user_b>

Upvotes: 7

Chris
Chris

Reputation: 18032

SELECT friends.friend_user_id FROM user, friends
INNER JOIN friends ON friends.user_id = user.user_id
WHERE user.user_id = 1 
     AND  friend.friend_user_id 
          IN (SELECT friends.friend_user_id 
             FROM user, friends
             INNER JOIN friends ON friends.user_id = user.user_id
             WHERE user_id = 4)

Upvotes: 0

Related Questions