Mysql Interesting Sql
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.
page revision: 1, last edited: 09 May 2017 22:19