Subscription Suspensions

["Ron Piggott" <ron.piggott@xxxxxxxxxxxxxxxxxx>]

Hi Everyone.

I manage an e-mail list.  I am trying to add a new function into it: suspensions.  The idea is that someone wouldn’t have to unsubscribe.  Instead there would be a record of their suspension in the table member_subscription_suspensions and their account wouldn’t be included as a database query result when sending out the daily e-mail.

There are three tables: Profile of the subscriber, subscriptions and subscription suspensions:

Membership Profiles are in this table:
CREATE TABLE IF NOT EXISTS `member` (
`record` int(10) NOT NULL AUTO_INCREMENT,
`first_name` varchar(40) NOT NULL DEFAULT '',
`last_name` varchar(40) NOT NULL DEFAULT '',
`email` varchar(40) NOT NULL DEFAULT '',
PRIMARY KEY (`record`),
UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3861 ;

Subscriptions are stored in this table:
CREATE TABLE IF NOT EXISTS `member_subscriptions` (
`subscription_reference` int(30) NOT NULL AUTO_INCREMENT,
`member_reference` int(10) NOT NULL DEFAULT '0',
`list` int(2) NOT NULL DEFAULT '0',
PRIMARY KEY (`subscription_reference`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2130 ;

I have designed this table to store subscription suspensions:
CREATE TABLE IF NOT EXISTS `member_subscription_suspensions` (
`reference` int(30) NOT NULL AUTO_INCREMENT,
`member_reference` int(11) NOT NULL DEFAULT '0',
`list` int(2) NOT NULL DEFAULT '0',
`subscription_begins` date NOT NULL DEFAULT '0000-00-00',
`subscription_expires` date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY (`reference`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

The common column between each table is:

member.record
member_subscriptions.member_reference
member_subscription_suspensions.member_reference

The only way I know how to write the query (which is wrong) is:

SELECT 
* 
FROM (
`member`
INNER JOIN `member_subscriptions` ON `member`.`record` = `member_subscriptions`.`member_reference`
) 
LEFT OUTER JOIN `member_subscription_suspensions` ON `member`.`record` = `member_subscription_suspensions`.`member_reference`
WHERE
CURDATE() BETWEEN `member_subscription_suspensions`.`subscription_begins` AND `member_subscription_suspensions`.`subscription_expires`
AND
`member_subscriptions`.`list` = 1

How do I change this query to exclude any one who has a suspension record for “today”. IE CURDATE() 

Thanks for your help.  Ron

Ron Piggott



www.TheVerseOfTheDay.info