CODEWITHSUNDEEP
sqlsql-server-2008
Jack James
Jack James

Reputation: 158

Search using multiple conditions with the IN Condition

I am basically trying to retrieve data and do some steps when 3 conditions are met. I have a table say contact columns (contactid (PrimaryKey), personid, firstname , lastname,email) Now I am basically trying to find the primary key i.e. the Contactid of the employees when multiple conditions are met i.e. where firstname = 'abc' and email = '[email protected]'. Now the issue mainly is because this table can have duplicates and employees might have more than 1 email address.

I was thinking of code like 

Select * from contact where firstname IN ('','','',) and lastname IN ('','','') and emailid IN ('','','')

I am testing but I am still not sure basically I am confused because of the IN's that i have in the query. Does the code gets sequentially executed in this case? Or if u can help me with some other approach that would be great.

Upvotes: 0

Views: 244

Answers (1)

kaineub
kaineub

Reputation: 162

What you want is something like the following:

SELECT *
FROM contact
WHERE (firstname = 'fname1' AND lastname = 'lname1' and emailid ='email1')
   OR (firstname = 'fname2' AND lastname = 'lname2' and emailid ='email2')
   OR (firstname = 'fname3' AND lastname = 'lname3' and emailid ='email3')

An IN list allows for many different combinations. Not too useful for this unfortunately.

Another option would be to put the allowed combinations in a separate table and use a JOIN:

SELECT C.*
FROM contact C JOIN allowedCombinations A
ON C.firstname = A.firstname AND C.lastname = A.lastname AND C.emailid = A.emailid

Upvotes: 3

Related Questions