How to run a Inner join on same table?

[nishant bugalia]

Hi,

I have a table UserDetails with following fields -
UserID (Primary key for table UserDetails)
UserName - User's Name
Surname - User's Surname.

There is one another table FriendData with following fields -
FID (Primary key for table FriendData)
UserID (UserID from table UserDetails)
FriendID (Friend's ID which is again User id from table UserDetails).


The table UserDetails contains info about users and table FriendData contain information about friends.

For ex. table UserDetails have the following data -
UserID UserName Surname
001 User1 Sur1
002 User2 Sur2
003 User3 Sur3
004 User4 Sur4
005 User5 Sur5
006 User6 Sur6
007 User7 Sur7
008 User8 Sur8
009 User9 Sur9
010 User10 Sur10

and the table FriendData have following data -
FID UserID FriendID
00a 002 009
00b 009 002
00c 002 007
00d 007 002
00d 001 002
00e 002 001
00f 001 007
00g 007 001
00h 003 002
00i 002 003
00j 009 004
00k 004 009

now, friends of user 002 are 009, 007, 001 & 003.
I want friends of friends for any given user.
In this case, for 002 the answer is - 001 & 004.
But as 001 is already friend of 002, so the answer is 004.

Kindly suggest query for this. I am using MSSql.

[code green]

An INNER JOIN ON the same table is no problem.
SImply write two joins giving each a different alias.
I don't understand your structure but

JOIN tablename AS friend1 JOIN sametablename AS friend2

Then to read fields from either join you need

SELECT friend1.fieldname AS 1_friend, friend2.fieldname AS 2_friend...