Reputation: 3856
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
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
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
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
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