AnsweredAssumed Answered

SQL question

Question asked by jurgmay on Feb 3, 2016
Latest reply on Feb 5, 2016 by jurgmay

Hello!

 

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

FROM Subscriptions

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!


Thanks.


Juerg



Outcomes