Newbie select question

markcasazza · #1 (**permalink**) 07-12-04, 22:07

I have 2 tables with email addresses. I need to get the email addresses from the "old" table that are not in the "new" table. A DBA I know suggested:
SELECT Email FROM Old WHERE Email NOT IN (SELECT Email FROM New)
It did not work.

TIA

Mark C

guelphdad · #2 (**permalink**) 07-13-04, 10:39

What does "it did not work" mean?
Did you get no results? Did you get an error message?
what version of MySQL are you running?

markcasazza · #3 (**permalink**) 07-13-04, 11:24

I got an error message pointing to the SELECT in the ().

I am running 3.23.58

guelphdad · #4 (**permalink**) 07-13-04, 14:52

The reason you are getting an error message is that you can't use subqueries before version 4.1

You can do this with a join though:

Code:

SELECT oldemailaddress 
FROM oldtable AS old
LEFT OUTER JOIN 
newtable as new
ON old.oldemailaddress = new.newemailaddress
WHERE new.newemailaddress IS NOT NULL

markcasazza · #5 (**permalink**) 07-13-04, 18:27

Not quite, but close. For the sake of anyone reading this later the following worked:

SELECT old.email FROM oldemail AS old LEFT OUTER JOIN newemail AS new ON old.email = new.email WHERE new.Email IS NULL

Thanks for the help!!

Internet Services

Web Development

Digital Marketing

Consumer Tech