CODEWITHSUNDEEP
mysql
user1001176
user1001176

Reputation: 1163

get friends of friend using mysql

I am making a query in which i want to get friends of friends but the problem is that if my friends are also friends it suggesting their ids too . Suppose user id is 1 and he has teo friends 2 and 3 and 2 has is friends with 4 and 3 and 3 is friends with 5 and 2 then its suggesting me 4,5 which it should but also suggesting 2,3 because they are friends and they both are also my friends which is wrong it should only suggest 4,5

My query is 

SELECT fr.friend_id 
FROM friend_list fl ,friend_list fr 
WHERE fl.login_id='27' 
AND fl.status='3' 
AND fl.friend_id=fr.login_id 
AND fr.status='3' 
AND fl.login_id!=fr.friend_id

Upvotes: 1

Views: 95

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

One way to do this is to add WHERE NOT IN predicate :

SELECT fr.friend_id 
FROM friend_list fl 
INNER JOIN friend_list fr  ON fl.friend_id = fr.login_id
                          AND fl.status = fr.status
WHERE fl.login_id = 27 AND fl.status = '3'
  AND fr.friend_id NOT IN(SELECT friend_id
                          FROM friend_list
                          WHERE login_id = 27 AND friend_id IS NOT NULL)

The subquery after the NOT IN will select the friends of the friend id you are passing. to exclude them.

This will give you only 4, 5 for the example you gave in your question:

| FRIEND_ID |
|-----------|
|         4 |
|         5 |

Note that: I used the explicit JOIN syntax instead of the old JOIN syntax you are using, it should be the same but it is recommended to use the new one.

Upvotes: 1

Related Questions