Find rows with parameter in one of two columns?

问题:

I have this table and need help

Friends:

My_E_Mail (VARCHAR) Friends_E_Mail(VARCHAR) 
[e-mail] [e-mail]
[e-mail] [e-mail]
[e-mail] [e-mail]

What I need to do is select either My_E_Mail or Friend_E_Mail that does not match a parameter that I plug in using? For example:

select Friend_E_Mail
From Friends 
WHERE Friend_E_Mail NOT IN(?)

My question is how to select one or the other column depending on that parameter if you plug in.

Let's say I plug in [e-mail] the only thing that should come up is Alester because Carl is friends with just alester not Greg and Jude.

Every time I make a statement myself I get it to show all of Alesters friends but when I plug in Carl I also see Jude and Greg even thought they are not friends. Also I "CAN NOT" create anther table.

Any help would be appreciated.

回答1:

Maybe this code will help you

select Friend_E_Mail
From Friends 
WHERE '[e-mail]' NOT IN(Friend_E_Mail, My_E_Mail)

回答2:

Try

DELCARE @email VARCHAR(50) = ?
WITH FRIENDS_CTE (My_E_Mail, Friend_E_Mail)
AS
(
  SELECT My_E_Mail, Friends_E_Mail
  FROM FRIENDS
  WHERE My_E_Mail = @email
  UNION 
  SELECT Friends_E_Mail, My_E_Mail
  FROM FRIENDS
  WHERE Friends_E_Mail = @email
)
SELECT Friend_E_Mail
FROM FRIENDS_CTE
WHERE My_E_Mail = @email