Send to users ON one list but NOT on other lists.

[DW]

Many users have requested the ability to send to users ON one list but NOT on other lists.

The only solution I have so far requires a User Selection "Custom Query" and MySQL 4.1+

This example selects users on list 1 but not 5 or 6:

Code: [Select]

a
 WHERE a.list = '1'
 AND cnf = '1'
 AND NOT
 EXISTS (
  SELECT *
  FROM lm_users b
  WHERE a.email = b.email
  AND b.list = '5'
  AND cnf = '1'
)  AND NOT
 EXISTS (
  SELECT *
  FROM lm_users c
  WHERE a.email = c.email
  AND c.list = '6'
  AND cnf = '1'
)
There is an interesting comment in the MySQL manual regarding the EXISTS statement: [Link]

EXISTS is supported from 4.1 on. Earlier versions of MySQL can try rewriting the query using a LEFT JOIN.

I hope to be able to take the time to figure this out soon.  

Regards

[hilco]

Dean,

Can you write me a query that is the same as above but does include multiple lists?

I wan to select all users that are on list 2,5 and 7 but none of these users should be at list 1, 3 or 6

something like:

Code: [Select]


a
 WHERE a.list = '2'
[ AND LIST = 5 AND LIST = 7 ]
 AND cnf = '1'
 AND NOT
 EXISTS (
  SELECT *
  FROM lm_users b
  WHERE a.email = b.email
  AND b.list = '5'
  AND cnf = '1'
)  AND NOT
 EXISTS (
  SELECT *
  FROM lm_users c
  WHERE a.email = c.email
  AND c.list = '6'
  AND cnf = '1'
)


OF course, the above query isn't working as of now but I could use a little help here. Thanks!

[onlinecoach]

Hi Dean - I am really not technically inclined but when I posted your code into the query - it didn't work - it said something about it not being proper syntax.

I want something similar I believe.  I have just sent an email to my customers on #6 list.  I now want to send another email to my #1 list - some of my customers (who just got an email from me) from #6 list will be on #1 list.

How can I create a query that sends out to my #1 list who have not received an email yet (#1 list - #6 list).

Karen.

[kyle]

Interesting that you ask - I've just finished using this code and had the same problem - you just need to make sure you upgrade the MySQL  4.1 or newer.  I had my hosts do that for me and it worked fine after that.  Also FYI, you have to include that "a" at the top of the query - that is part of it    Good luck,
--Kyle

[onlinecoach]

Thanks Kyle - I'll try that out!

Karen.

[asr_guy]

I have lists of emails that I want to be filtered out from the mailing. This is called "suppression list" and common in affiliate marketing where the merchant has a list of unsubscribes that all affiliates need to supress from their mailings.

I've searched the forum and help but don't see much talk on this topic other than this sticky thread.

How can I set up a mailing to a list, and specify lists that I don't want the mailing to go to???

I hope you're not going to say we need to use SQL code...

[asr_guy]

Hi - anyone have experience using suppression lists? Am I the only one?

Help!

[DW]

You could either use a custom script to remove users from one list who appear on another list or use User Selection and a custom query such as mentioned in this post.

I plan on adding the ability to remove users uploaded via the Import process in the future.

Regards

[carlyn]

I've had success with the above example - thanks Dean.

But -- I'm now wanting to exclude many lists, which makes my code longer. It seems that I'm being restricted to about 350 characters.

Is there a way I can extend the character limit for queries?  Or perhaps someone can help me reduce/simplify my code? (currently this code is being cut off halfway though line 5.)

Goal with this code: Include List #2, but exclude lists #3,8,9,10. (Code works with fewer lists excluded, but I'm just being limited by characters.)

Code: [Select]

a
 WHERE a.list = '2' AND cnf = '1'
AND NOT EXISTS (SELECT * FROM lm_users b WHERE a.email = b.email   AND b.list = '3' AND cnf = '1')  
AND NOT EXISTS (SELECT * FROM lm_users c WHERE a.email = c.email AND c.list = '8'  AND cnf = '1')  
AND NOT EXISTS (SELECT * FROM lm_users d WHERE a.email = d.email AND d.list = '9'  AND cnf = '1')
AND NOT EXISTS (SELECT * FROM lm_users e WHERE a.email = e.email AND e.list = '10'  AND cnf = '1')

[DW]

What you can do is increase the size of the field holding the data.  That can be done with PhpMyAdmin and the following query:

Code: [Select]

ALTER TABLE lm_selectd CHANGE q q TEXT NOT NULL
This change will be automatically applied to all installations after the next update.

Regards

[carlyn]

Worked perfectly. Thank you.

[mike2]

Good luck getting support here, it's probably not gonna happen.

You could probably contact Brett at BSG and pay for some support, I do highly recommend him.

But if you are looking for something better including full hosting check out our new service.

No spam allowed of course, very strict on that... It also comes with a bit newer autoresponder than Listmail but you should also be able(?) to use listmail if you wanted...

Here's my shameless plug for it:
http://www.extremeleadprogram.com/extreme-autoresponder-realtime-web-host