Opt Out

[melanie]

We have multiple lists, and would like to give people the option of opting out of any future mailing ever - even if we add a new list later. We were thinking that we could just create an Opt Out list, and use user selection to avoid mailing them. Can I do this with a custom query - and if so - what would that query look like?

[melanie]

Okay - so I found the sticky topic above with the NOT EXISTS query - which errors out on me - we're running 4.0.25 MySQL version - so I'm trying to rewrite with a left join:

List 8:  List we want to send to
List 1:  List we don't want to send to

list 8 has 18226 active users, list 1 has 1 active user - so the results of the query should be 18225 users.

Query:
a left join lm_users as b on a.email=b.email where (a.list='8' and a.cnf='1') and (b.list='1' and b.cnf='1')

but I'm getting:
SELECT * FROM lm_users a left join lm_users as b on a.email=b.email where ((a.list='8' and a.cnf='1') and (b.list !='1' and b.cnf='1')) group by a.email

18051 users matched

What am I doing wrong?

[DW]

I'm not a left join expert but I'm working on it...

Right now I'm checking pages in these results:

http://www.google.com/search?q=left+join+instead+of+not+exists

http://www.google.com/search?q=mysql+outer+join+instead+of+not+exists

You'll probably get this one before me...

You know, an upgrade to MySQL 4.1.x would be pretty painless...  I bet it would take your system admin less than 5 minutes.

[melanie]

I "created" a similar table in MS Access to see if I could figure the query out that way - nothing.  You have to create the other query first, then bring it in to the final query.  Since listmail is incapable of that (for obvious reasons - can you imagine the system resources required)  - I've asked the sysadmin of our managed server when they will be upgrading us.  Until then - if someone figures this out  - let me know - but frankly - I'm not sure it's possible.

[DW]

The upgrade to MySQL 4.1 should be very quick and easy because no config files, etc. will need to be updated (from 3.x -> 4.x+, however, things changed a bit).  It should be practically instant - a simple matter of running the appropriate automated package update/install command from the command prompt.  Hopefully your host knows what they're doing and can take care of it for you within a reasonable amount of time.

Regards