homiee Posted February 20, 2008 Share Posted February 20, 2008 heya all,I have this table:OwneruserID | FriendUserId1 | 22 | 12 | 33 | 23 | 44 | 2This table means: User 1 is friend with User 2, User2 is friend with User3 and User3 is friend with User4I'm looking for a select statement to select the friends of friends of my friends (but only select those who are not my friends or friends of my friends)Help me out.. I've been trying to solve this for daysThanks in advance!homiee Link to comment Share on other sites More sharing options...
Reg Edit Posted February 20, 2008 Share Posted February 20, 2008 I'm looking for a select statement to select the friends of friends of my friends (but only select those who are not my friends or friends of my friends)Help me out.. I've been trying to solve this for daysThanks in advance!homieeProbably a few ways to do it, but how about something like this. I've called the table friends:declare @me intset @me = 1 -- example value only--set as requiredselect distinct fofof.frienduserid from friends finner join friends fof on fof.owneruserid = f.frienduseridinner join friends fofof on fofof.owneruserid = fof.frienduseridwhere f.owneruserid = @me and fofof.frienduserid not in (select fof2.frienduserid from friends f2 inner join friends fof2 on fof2.owneruserid = f2.frienduserid where f2.owneruserid = @me)and fofof.frienduserid not in (select frienduserid from friends where owneruserid = @me) Assuming you may also want to exclude yourself from the results, you'll need to add a further condition: and fofof.frienduserid <> @me Link to comment Share on other sites More sharing options...
homiee Posted February 20, 2008 Author Share Posted February 20, 2008 Hey mate, thanks alot for the reply, I made this code - I called the table SocialLinks. Can you confirm this does the same as you did?My user id is 2thanks! SELECT FriendUserId,OwnerUserId, 3 AS Level FROM SocialLinks socLink1 WHERE OwnerUserId in( SELECT FriendUserId FROM SocialLinks socLink1 WHERE OwnerUserId in( SELECT FriendUserId FROM SocialLinks WHERE OwnerUserId = 2) AND not FriendUserId = 2 )AND FriendUserId not in(SELECT frienduserid FROM SocialLinks where owneruserid = 2 unionselect frienduserid from sociallinks where owneruserid in (select frienduserid from sociallinks where owneruserid = 2) Link to comment Share on other sites More sharing options...
Reg Edit Posted February 20, 2008 Share Posted February 20, 2008 Hey mate, thanks alot for the reply, I made this code - I called the table SocialLinks. Can you confirm this does the same as you did?My user id is 2thanks!... Looks likely to be an equally valid way, and there are sure to be many others. Just make sure it tests correctly, performs adequately, and is comprehensible to people who'll need to look at that code in the future and understand what it does... Note that you haven't excluded yourself from the results, so if that is a requirement, then you still need that extra condition I suggested at the end. (Try it with 4 instead of 2 and you'll see that 4 is included in the results.) Link to comment Share on other sites More sharing options...
homiee Posted February 20, 2008 Author Share Posted February 20, 2008 Thanks alot Reg Edit, you helped me a bunch! Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.