mysql list comparison problem with query logic

Question

Hello,

I am comparing two lists of email addresses in mysql - listA and listB (both of which are views). Originally, I wanted to find all of the email addresses in listA that are not in listB. I successfully accomplished that with:

$comparison_query = mysql_query("SELECT DISTINCT email_addresses FROM listA WHERE email_addresses NOT IN (SELECT DISTINCT email_addresses FROM listB) ORDER BY email_addresses");

Now, I want to find all of the email addresses in listA that are not in listB OR an exception table. I have tried to do this with:

$comparison_query = mysql_query("SELECT email_addresses FROM listA WHERE email_addresses NOT IN (SELECT DISTINCT email_addresses FROM ((SELECT email_addresses FROM listB) UNION (SELECT email_addresses FROM exception))) ORDER BY email_addresses");

However, this is not working. Can anyone see where I am going wrong?

Thanks!

Answer 1

I think something like this should do the trick:

SELECT email_addresses FROM listA
WHERE email_addresses NOT IN
(SELECT DISTINCT email_addresses FROM FROM listB)
AND email_addresses NOT IN
(SELECT email_addresses FROM exception);

Answer 2

USE AND instead of UNION

 mysql_query("SELECT email_addresses FROM listA
        WHERE email_addresses  NOT IN
        ( SELECT DISTINCT email_addresses FROM listB) AND 
         email_addresses  NOT IN
        (SELECT DISTINCT email_addresses FROM exception)) 
        ORDER BY email_addresses");