sql query question! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sql query question!

Author

Topic

alooma
Starting Member

8 Posts

Posted - 2008-04-11 : 14:57:10

hi i have a table like this
ID,userID,friendUserID
1 1 2
2 1 3
3 2 1
4 3 2

1's friends are 2,3
3's friends is just 2

2's friend is 1 at same time 1 and 2 is mutual friend

so i want to ask that how we get the mutual friends?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 15:52:49

[code]DECLARE @Sample TABLE (ID INT, userID INT, friendUserID INT)

INSERT @Sample
SELECT 1, 1, 2 UNION ALL
SELECT 2, 1, 3 UNION ALL
SELECT 3, 2, 1 UNION ALL
SELECT 4, 3, 2

SELECT s1.userID AS User1,
s2.userID AS User2,
s2.friendUserID AS mutualFriendID
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.friendUserID = s1.friendUserID
WHERE s1.userID < s2.userID[/code]

E 12°55'05.25"
N 56°04'39.16"

bfoster
Starting Member

30 Posts

Posted - 2008-04-11 : 16:03:35

Maybe I misunderstood the question, but using the @Sample table given above, I thought this is what you were trying to find.

SELECT s1.userID AS User1,
s2.userID AS User2,
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s1.userID = s2.friendUserID AND s1.friendUserID = s2.userID
WHERE s1.userID < s2.userID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 16:14:21

Oh, mutual friends or common friends?

E 12°55'05.25"
N 56°04'39.16"

alooma
Starting Member

8 Posts

Posted - 2008-04-11 : 16:27:03

it is working thank u much

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 16:39:41