Excluding Addresses

[erik]

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

[chris6]

I too would also like this feature.

[DW]

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

[chris6]

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'
 );

[DW]

This is a v1.85 feature [changes].  You can find the option on the "User Selection" -> "Add Selection" page.

Regards

[chris6]

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.

[chris6]

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.

[chris6]

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?

[chris6]

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'
 )

[DW]

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.

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).

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