Need help with two column query

[ShawnMolloy]

I'm making a community site database. I have a table that forms the 'friend' relationship between two users. The table looks like this:

UserId(int) | FriendId(int)

I want to give my stored proc one paramater, the userId of the user whos 'friends list' I'd like to see. I only have one row per friend relationship, so if YOU add someone then you both are friends and if THEY add you you are both friends. This query doesn not work:

SELECT UserId, FriendId
FROM FRIENDS
WHERE userid=7 OR friendid=7

That will return this:

UserId FriendId
----------- -----------
11 7
12 7
8 7
7 9


There are duplicate rows and sometime 7 is in other colums. I just want one column where it lists all the numbers that ARE NOT 7.

Any ideas??

THANK YOU!!

-- shawn

 

[r937]

select UserId
  from FRIENDS
 where FriendId = 7
union
select FriendId  
  from FRIENDS
 where UserId = 7

r937.com | rudy.ca