CODEWITHSUNDEEP
sqlsql-serversql-server-2008
James Putin
James Putin

Reputation: 13

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) 
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]

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 [email protected] 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.

Upvotes: 1

Views: 154

Answers (2)

Ram
Ram

Reputation: 3091

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

Upvotes: 0

Robert
Robert

Reputation: 25753

Maybe this code will help you

select Friend_E_Mail
From Friends 
WHERE '[email protected]' NOT IN(Friend_E_Mail, My_E_Mail)

Upvotes: 2

Related Questions