CODEWITHSUNDEEP
mysqlsql
Pankaj Pareek
Pankaj Pareek

Reputation: 3856

Sql to get all the friends of friends who's not my friend

I'm working on a "Possible Friends" feature. where i need to show all the friends of friends who's not my friend and also not send me or not having my pending request

FRIENDSHIPS
user_id
friend_id
status  (0 = pending, 1 = approved)

For each friendship I make two records. Say users 1 and 2 become friends... I would make a record where user_id=1,friend_id=2 and another where user_id=2, friend_id=1 in the friendships table.

when first user send request, set status to 0 and when friend accept request then I would update both row with 1

How would I make a sql query that suggests "possible friends" based on friends of my friends ?

Upvotes: 0

Views: 4092

Answers (4)

Jineesh Uvantavida
Jineesh Uvantavida

Reputation: 671

Please try this :)

SELECT  f2.friend_id<br>
FROM    FRIENDSHIPS f1<br>
JOIN    FRIENDSHIPS f2<br>
ON  f1.friend_id=f2.user_id<br>
WHERE   f2.friend_id NOT IN (select friend_id from FRIENDSHIPS where user_id=@user_id)<br> 
    AND f1.user_id=@user_id <br>
    AND f2.friend_id!=@user_id<br>

Upvotes: -1

vice
vice

Reputation: 605

I think you could also use a sub-query (?) to retrieve the same information.

SELECT friend_id FROM FRIENDSHIPS WHERE user_id IN (SELECT friend_ID FROM FRIENDSHIPS WHERE user_id=@YOURUSER_ID AND status=1) AND friend_id<>@YOURUSER_ID 

SELECT friend_id 
FROM FRIENDSHIPS
WHERE user_id IN
(SELECT friend_ID FROM FRIENDSHIPS WHERE user_id=@YOURUSER_ID AND status=1)
AND friend_id NOT IN
(SELECT friend_ID FROM FRIENDSHIPS WHERE user_id=@YOURUSER_ID AND status=1)
AND friend_id<>@YOURUSER_ID

I think will work but the join method is much simpler.

Upvotes: 0

Marada
Marada

Reputation: 39

Try this :-)

select * from FRIENDSHIPS pf 
JOIN FRIENDSHIPS f ON pf.friend_id=f.user_id AND pf.status=1 AND f.status=1   
JOIN FRIENDSHIPS me ON me.user_id = f.friend_id AND me.user_id = MY_USER_ID AND me.status=1 AND f.status=1 
WHERE me.user_id <> pf.friend_id AND me.friend_id <> pf.user_id

Upvotes: 0

Hogan
Hogan

Reputation: 70528

Here you go... simple join

SELECT F2.friend_id
FROM FRIENDSHIPS F
JOIN FRIENDSHIPS F2 ON F.friend_id = F2.user_id
WHERE F2.friend_id NOT IN (SELECT friend_ID FROM FRIENDSHIPS WHERE user_id = @user_id)
  AND F.user_id = @user_id

Upvotes: 7

Related Questions