Custom Query

[lambertb]

Hi Dean

How do I write a custom query to:

compare all the subcribers to "list 1" and then see if they are on "list 2, 3, 4, 5, 6, 7, or 8"  and if they're NOT on any of those 8, send them an email?

I used the rules "wizard" to create a compilation of all 9 lists (and it de-dupes when you do that).  So, maybe I can just look at that one "compiled list" and see if "list 1" people are on it.  If they're not on it, then mail them.

Is this do-able?

Brian

[DW]

Brian,

This post is a start:
http://listmailpro.com/forum/index.php?topic=.msg3914#msg3914

I suppose the query could be as follows:

Code: [Select]

SELECT *
 FROM lm_users a
 WHERE a.list = '1'
 AND cnf = '1'
 AND NOT
 EXISTS (
  SELECT *
  FROM lm_users b
  WHERE a.email = b.email
  AND 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'
 )
 AND NOT
 EXISTS (
  SELECT *
  FROM lm_users d
  WHERE a.email = d.email
  AND d.list = '4'
  AND cnf = '1'
 )
 AND NOT
 EXISTS (
  SELECT *
  FROM lm_users e
  WHERE a.email = e.email
  AND e.list = '5'
  AND cnf = '1'
 )
 AND NOT
 EXISTS (
  SELECT *
  FROM lm_users f
  WHERE a.email = f.email
  AND f.list = '6'
  AND cnf = '1'
 )
 AND NOT
 EXISTS (
  SELECT *
  FROM lm_users g
  WHERE a.email = g.email
  AND g.list = '7'
  AND cnf = '1'
 )
 AND NOT
 EXISTS (
  SELECT *
  FROM lm_users h
  WHERE a.email = h.email
  AND h.list = '8'
  AND cnf = '1'
 );