difficult SP

I have the following table:
tblFriends
OwnerCode FriendCode
7 10
7 14
10 7
10 12
10 13
12 10
13 10
13 18
14 7
18 13

I need a SP which return the following (im unsure about the best return datatype and the sql statement):

I want return all friendcodes of user nr 7 (10 and 14)
and I want to return all friendcodes of user 10 and 14 (7,12,13,7) WITHOUT user 7

(if possible WITHOUT the use of a temptable!)

SELECT FriendCode

FROM tblFriends

WHERE OwnerCode=7

UNION

SELECT t2.FriendCode

FROM tblFriends t1

JOIN tblFriends t2 ON (t2.OwnerCode=t1.FriendCode)

WHERE t1.OwnerCode=7 AND t2.FriendCode<>7

That's not really a SP, but you can put it in one if you want.

|||GREAT!!! :-D|||hmm...it does what I want but I need a bit more...
I need to check if a certain value (e.g. 6) is in the result set...

I tried the following (which does not work):

SELECT

COUNT(*)FROM

(

SELECT

FriendCode

FROM

tblFriends

WHERE

OwnerCode=5

UNION

SELECT

t2.FriendCode

FROM

tblFriends t1

JOIN

tblFriends t2ON(t2.OwnerCode=t1.FriendCode)

WHERE

t1.OwnerCode=5AND t2.FriendCode<>5

)

WHERE

FriendCode=6I get the error:

Incorrect syntax near the keyword 'WHERE'.

|||

Close. You need to give your subquery an alias. For example, change "WHERE" to "t1 WHERE" or

SELECT COUNT(*)

FROM ( ... ) t1

WHERE ...