ShawnMolloy
MIS
I am trying to determine what "Friends" a "User" has, based on a lookup table that holds a friends/user relationship information. The table looks like this:
UserId | FriendId
-----------------
1 | 3
7 | 9
4 | 3
1 (Shawn) is friends with 3 (Ashlee), so therefore Ashlee is also friends with shawn. I can't write a query like
SELECT * FROM FRIENDS WHERE FriendId=3
because that is only one half of the relationship (because 3 could also be the userid). This is my solution, but when I run it and search by the FriendID (Second part of the union) All I get is two instances of the UserId i searched for:
To see this in action go here and click on friends. You see all of Ashlees friends. If you click on Jenny's profile and click Friends you should see Ashlee, but instead you just see two instances of Jenny. I hope I am explaining this clearly!! Its pretty confusing.
Shawn Molloy
Seattle, WA
UserId | FriendId
-----------------
1 | 3
7 | 9
4 | 3
1 (Shawn) is friends with 3 (Ashlee), so therefore Ashlee is also friends with shawn. I can't write a query like
SELECT * FROM FRIENDS WHERE FriendId=3
because that is only one half of the relationship (because 3 could also be the userid). This is my solution, but when I run it and search by the FriendID (Second part of the union) All I get is two instances of the UserId i searched for:
Code:
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY A.UserId)
AS rowNum, A.*, B.UserName FROM WiseTopic_Friend A
INNER JOIN WiseTopic_User B ON A.FriendId = B.UserId
WHERE a.userid=@UserId)
AS X
WHERE rowNum between 1 and 12
UNION
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY A.UserId)
AS rowNum, A.*, B.UserName FROM WiseTopic_Friend A
INNER JOIN WiseTopic_User B
ON A.FriendId = B.UserId
WHERE a.friendid=@UserId)
AS X
WHERE rowNum between 1 and 12
To see this in action go here and click on friends. You see all of Ashlees friends. If you click on Jenny's profile and click Friends you should see Ashlee, but instead you just see two instances of Jenny. I hope I am explaining this clearly!! Its pretty confusing.
Shawn Molloy
Seattle, WA