I have a subscription table which stores subscription information for various publications. The table stores information such as the publication name, subscription start date, end date, start issue number, end issue number.
The Subscriptions table stores expired, current and pending subscriptions which are identified through the fields 'isExpired', 'isCurrent' and 'isPending' respectively. These fields store boolean values.
My problem is this... I need a list of customers and the magazines that they no longer subscribe to but ONLY if they DO NOT have a pending subscription for that publication.
So if a customer DID subscribe to Publication A in the past (i.e. there's a subscription record marked 'isExpired = 1') but they have a pending subscription, starting next month for example, for that same publication then I DON'T want to include that customer/publication combination.
Here's a query that I use to identify the lapsed subscriptions (for brevity I've changed the full number of fields in the SELECT) -
SELECT Subscriptions.publicationName, Customer.fullName, Customer.address
LEFT JOIN Customers
ON Subscriptions.id_Customer = Customers.id
LEFT JOIN Publications
ON Subscriptions.id_Publication = Publications.id
WHERE Subscriptions.endIssueNumber < Publications.currentIssueNumber
AND Customers.isDeceased = 0
AND Customers.isDeleted = 0
Hopefully I've explained this well enough!