I am trying to improve on a nested MySQL query, using the following table structure (omitting a few dozen not relevant to my question):
ID | email (all subscribers to our email list)
ID | mail_title (each marketing email)
ID | user_id | mailing_id (each user who was sent each mailing)
ID | user_id | mailing_id (each user who opened each mailing)
ID | user_id | mailing_id (each user who took the target action on each mailing)
I inherited this query that I have been using to get the open rate and action rate for each mailing:
SELECT subq_opens.numopens/subq_sends.numsends as Open_Rate, subq_actions.numactions/subq_sends.numsends as Action_Rate, FROM (SELECT COUNT(DISTINCT sends.userID) AS numsends FROM tbl_sends WHERE mailing_id = 5694) AS subq_sends, (SELECT COUNT(DISTINCT opens.userID) AS numopens FROM tbl_opens WHERE mailing_id = 5694) AS subq_opens, (SELECT COUNT(DISTINCT actions.userID) AS numactions FROM tbl_actions WHERE mailing_id = 5694) AS subq_actions
Not too elegant, but functional, even though I have to change the mailing ID in multiple places every time.
However, now I need to change that WHERE statement to something a great deal more complicated, and I don't want to have to repeat the WHERE statement so many times.
Is there a way I can reformulate this without the redundant
I have tried various nesting schemes, but can't seem to find a way to get it right. I can't change anything about the table structure; that is set in stone. Thank you in advance for your help.