Jump to content

Sophisticated SELECT statement.. help me do the math


homiee
 Share

Recommended Posts

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

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!homiee
Probably 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

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

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...