Matt Matt -4 years ago 59
SQL Question

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

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?

Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download