Hi thought i'd try here see if anyone can help solve this quicker.
First off we have a email database.
The main table is the email data, all unique rows per email address with a unique id.
Next we have another table which logs the data of each time an email has been attempted to send such as
The part i'm having trouble with is a query that pulls the next emails a campaign is up to
( SELECT * FROM `the_main_table` as `mt` LEFT JOIN `sentdata` AS `sd` ON mt.emailid = sd.emailid WHERE ##......## sd.campaign_id = 'send2' GROUP BY mt.emailid ORDER BY MAX(sd.`datesent`) ASC HAVING MAX(sd.`datesent`) LIMIT 0,500 ) UNION ( SELECT * FROM `the_main_table` as `mt` WHERE ##......## mt.emailid NOT IN(SELECT `emailid` FROM `sentdata` WHERE `campaign_id` = 'send2') GROUP BY mt.emailid LIMIT 0,500 )
The needed results for the query is an ordered list of a set quantity (eg. 500) of emails that haven't been sent to OR if everything has been sent the emails that haven't been sent to for the longest time.
The problem with the current one I worked out above is the quantity, the first union pulls an ordered list of the 500 oldest it has already sent to(if there are any), then the union below pulls 500 it hasn't sent to(if there are any). For new campaigns it will pull 500, which is good afterwards it will pull the same 500 plus 500 new ones - which we can't have.
Can anyone think of a different approach to get the desired results?