Mysql Interesting Sql

mysql

I have a table named bcast_queue which stores all the email campaigns that were every sent through my system, and another table named activity_queue which records all the activities or interactions that the user took (email sent to the user, user open the email, click on a link inside the email, bounce, optout, etc). I need to find all the broad cast ID that has 'HTML View' but not 'Sent' activities:

select
   bcastID 
from
   (
      select
         a.bcastID,
         count(*) cnt 
      from
         activity_queue a,
         bcast_queue b 
      where
         a.bcastID = b.bcastID 
         and b.start_date between '2016-01-01 00:00:00' and '2017-05-08 00:00:00' 
         and b.status = 'C' 
         and a.Activity = 'HTML View' 
      group by
         bcastID 
      having
         cnt > 0
   )
   as b 
where
   bcastID NOT IN 
   (
      select
         bcastID 
      FROM
         (
            select
               a.bcastID,
               count(*) cnt 
            from
               activity_queue a,
               bcast_queue b 
            where
               a.bcastID = b.bcastID 
               and b.start_date between '2016-01-01 00:00:00' and '2017-05-08 00:00:00' 
               and b.status = 'C' 
               and a.Activity = 'Sent' 
            group by
               bcastID 
            having
               cnt > 0
         )
         b
   )
;

The above SQL statement works by using two sub-select and a NOT IN clause. Perhaps there is another approach using a self-join. NOT SURE HOW TO DO THIS YET.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License