Subscription Suspensions

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