[RESOLVED] Extracting Duplicates from Two Separate Lists

artnow

Hi All -

I want to build a script that can extract email address on an email suppression list that happen to be in my own email list. Trouble is, I have no idea on what terms I need to search for on the forums or google.

Would someone mind giving me an idea as to where to start ... perhaps point me in the correct direction in terms of vocabulary.

Thanks.

sneakyimp

So you have your email list and you want to avoid mailing folks who are on an exclusion list, right?

Unless your lists are quite small (1,000 names or less) then you probably should use a database approach. You put your active email list in one table:

email_list
  -- id
  -- email

and you put the exclusion list in another table

email_exclude
  -- id
  -- email

and then you can get all the emails that are NOT in the exclusion list by doing something like this:

SELECT el.email FROM email_list el LEFT JOIN email_exclude ee ON ee.email=el.email WHERE  ee.email IS NULL

I haven't tested that but it should be close to what you need.

artnow

That makes a lot of sense - Thanks! Having two tables and comparing them with a select statement using "join" - I knew there was a word for it!

I assume that rather than finding all the emails that are not duplicates I can also find those that are by using something like WHERE ee.email > 1.

I'll get to work and see what I can put together ... then I'll post it here for review.

sneakyimp

artnow;10893610 wrote:
I assume that rather than finding all the emails that are not duplicates I can also find those that are by using something like WHERE ee.email > 1.

I think it would be WHERE ee.email IS NOT NULL

There might be an issue of case sensitivity here but perhaps not. It would depend on either your DBMS settings or the collation of your tables. I think by default that MySQL is not case sensitive but I could be wrong. You might want to normalize your email addresses to all lowercase or something.

artnow

I just realized that there really is nothing to post for review since your suggestion worked beautifully! Thank you!

sneakyimp

Great! Plz remember to mark the thread 'resolved'.