How can I do a compare when the second is a sub select that returns multiple values?

Question

Here's my SQL statement:

SELECT DISTINCT a.* 
   FROM OWU_Nomination as a, 
        Merchants as b WHERE a.CallFlag = 'False' 
   AND a.nominatedDate < DateAdd(hour, -24, getdate()) 
   AND a.email != (SELECT c.Email from Members as c where c.MemberID = b.MemberID)

The problem here is that the sub select after a.email != returns multiple records. I would like to see if a.email is in any of the emails returned from the subselect, is this possible? Is there a Contains function or something similar that will do this?

Thanks,
Matt

Answer 1

From your question, I'm not sure if you want it to be in the set or not in the set.

If it must be in the set, use this clause:

AND a.email IN (SELECT c.Email from Members as c where c.MemberID = b.MemberID)

otherwise:

AND a.email NOT IN (SELECT c.Email from Members as c where c.MemberID = b.MemberID)