ListMailPRO Email Marketing Software Forums

ListMailPRO Email Marketing Software Forums => Development, Suggestions => Topic started by: erik on April 18, 2006, 11:30:27 am

Title: Excluding Addresses
Post by: erik on April 18, 2006, 11:30:27 am

Hi,

I was just trying to compile a list using user selection.

I want to send an email to listas a,b,c - but remove everyone who is on list d.

The user selection tool has AND OR choices, but where is the NOT?  Is there a way to do this with some sort of wildcard search?

Thanks!
Erik

Title: me too
Post by: chris6 on May 04, 2006, 03:09:38 pm

I too would also like this feature.

Title: Excluding Addresses
Post by: DW on May 04, 2006, 06:41:53 pm

NOT is not currently an option due to its complexity (and I believe MySQL 4+ requirement).

A custom query (User Selection -> Edit/Add -> Custom Query) to email all users who are on list 1 and 2 but not 3 might be:

Code: [Select]

SELECT *
 FROM lm_users a
 WHERE (a.list = '1'
 AND cnf = '1')
 OR (b.list = '2'
 AND cnf = '1')
 AND NOT
 EXISTS (
  SELECT *
  FROM lm_users c
  WHERE a.email = c.email
  AND c.list = '3'
  AND cnf = '1'
 );
Here's a similar post:
http://listmailpro.com/forum/index.php?topic=1109.0

Regards

Title: Excluding Addresses
Post by: chris6 on May 08, 2006, 01:27:42 am

Where are you starting this query in the MYSQL plesk area? or in Listmail Pro, I can find a Custom Query section?




NOT is not currently an option due to its complexity (and I believe MySQL 4+ requirement).

A custom query (User Selection -> Edit/Add -> Custom Query) to email all users who are on list 1 and 2 but not 3 might be:
Code:
SELECT *
 FROM lm_users a
 WHERE (a.list = '1'
 AND cnf = '1')
 OR (b.list = '2'
 AND cnf = '1')
 AND NOT
 EXISTS (
  SELECT *
  FROM lm_users c
  WHERE a.email = c.email
  AND c.list = '3'
  AND cnf = '1'
 );

Title: Excluding Addresses
Post by: DW on May 08, 2006, 06:06:57 pm

This is a v1.85 feature [changes (http://listmailpro.com/changes.php)].  You can find the option on the "User Selection" -> "Add Selection" page.

Regards

Title: Do not include Feature
Post by: chris6 on May 17, 2006, 01:27:59 am

custom query (User Selection -> Edit/Add -> Custom Query) to email all users who are on list 1 and 2 but not 3 might be:
Code:
SELECT *
 FROM lm_users a
 WHERE (a.list = '1'
 AND cnf = '1')
 OR (b.list = '2'
 AND cnf = '1')
 AND NOT
 EXISTS (
  SELECT *
  FROM lm_users c
  WHERE a.email = c.email
  AND c.list = '3'
  AND cnf = '1'
 );


Hi Dean
Am I to assume that first of all you have to creat the Search Query prior to then creating the custom query?

if so can you pleae rewrite the above code for me if the first query is called 2-End and the list I want to check against that they are not on is called VIP.

Title: Excluding Addresses
Post by: chris6 on May 17, 2006, 01:36:28 am

addendum to above
in the code is the number of the lists the user query name you create as a search term or is the current list name that I use for all the lists of email users that are stored in my listmail pro database.

Title: Excluding Addresses
Post by: chris6 on May 17, 2006, 01:58:30 am

second addendum
and if it is from the list name as opposed to the query name, can you only send it to every on the whole list regardless of where they are in your sequence of follow ups?

Title: Excluding Addresses
Post by: chris6 on May 17, 2006, 02:02:42 am

MySQL Error: You have an error in your SQL syntax near 'SELECT * FROM lm_users a WHERE (a.list = '1' AND cnf = '1') OR (b.li' at line 1


this is all i every get using your code

SELECT *
 FROM lm_users a
 WHERE (a.list = '1'
 AND cnf = '1')
 OR (b.list = '2'
 AND cnf = '1')
 AND NOT
 EXISTS (
  SELECT *
  FROM lm_users c
  WHERE a.email = c.email
  AND c.list = '3'
  AND cnf = '1'
 )

Title: Excluding Addresses
Post by: DW on May 17, 2006, 09:14:37 am

Quote

MySQL Error: You have an error in your SQL syntax near 'SELECT * FROM lm_users a WHERE (a.list = '1' AND cnf = '1') OR (b.li' at line 1
this is all i every get using your code

I believe your server must be running MySQL 4+ for these queries un order to use the NOT EXISTS function and "FROM lm_users a" syntax.  This is one of the reasons the feature is not yet built right into the program. :(

Quote

in the code is the number of the lists the user query name you create as a search term or is the current list name that I use for all the lists of email users that are stored in my listmail pro database.

The list number is just that, the List Ref # from the List Settings page, also seen under Current List.  To search a field for a certain value will require an addition to the statement.  Unfortunately, I'm not an expert on these complex queries yet, but I might be able to come up with something if you want to do this (search a user field AND limit by list).

Quote

Am I to assume that first of all you have to creat the Search Query prior to then creating the custom query?

if so can you pleae rewrite the above code for me if the first query is called 2-End and the list I want to check against that they are not on is called VIP.

Currently a search query is not part of the the code provided and you cannot use standard selection rules alongside a custom query.

Regards