Select with subqueries and substrings

[KarlMessner]

I have a mailing list for a fan club. The newsletter has a dynamic
image in it that passes back the reader's id number and inserts a row
into a readership table that says that THAT reader read THAT newsletter
at this time.


I wrote a fairly clever (for me) SQL Statement that pulled whether or
not we had any "hits" from folks with aol.com addresses, yahoo.com
addresses, etc. So we could see who, if anybody was blocking our 100%
double opt-in fan club newsletter. So, Now, I have nice dashboard that
shows me that none of the major providers is blocking us.


NOW, what I want to do is try to find a list of domains that MIGHT be
blocking us, but they're too small for me to notice. So, I want to find
all the domains in the user database that don't have a single hit. the
tricky part is that the domain is a substring of the email field. So, I
want to find domains that are present in the user table, but not
present in the confirmed readership table.


my fan_profiles table has fan_id, fan_email fields (and 40 others)


my newsletter_readers table has newsletter_reader_id, read_date, method
(via web archive or email) and many others.


I can get the domain by:
substring(fan_email,1+locate('@', fan_email))


I can get all the emails that didn't read the newsletter with:

SELECT 
        * 


FROM 
        fan_profiles 
                LEFT JOIN 
        newsletter_readers 


ON 
        newsletter_readers.newsletter_reader_id = fan_profiles.fan_id 


WHERE 
        newsletter_readers.newsletter_reader_id is null

But I can't connect the dots in my head. One way would be to pull all
the successful reader's domains, and the unsuccssful domains and do
some XOR thing on those two lists. I know this can happen. Can someone
help me get past this!

 

[r937]

select substring(fan_email
                ,1+locate('@',fan_email)) 
          as domain 
  from fan_profiles 
left outer
  join newsletter_readers 
    on fan_profiles.fan_id 
     = newsletter_readers.newsletter_reader_id
group
    by domain
having count(newsletter_reader_id) = 0

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)