2 Replies Latest reply on Feb 5, 2016 4:15 AM by jurgmay

    SQL question

    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



        • 1. Re: SQL question
          user19752

          Adding "isExpired and not isPending"

           

          SELECT s1.publicationName, Customer.fullName, Customer.address

          FROM Subscriptions s1

          LEFT JOIN Customers

          ON s1.id_Customer = Customers.id

          LEFT JOIN Publications

          ON s1.id_Publication = Publications.id

          WHERE s1.endIssueNumber < Publications.currentIssueNumber

          AND Customers.isDeceased = 0

          AND Customers.isDeleted = 0

          AND s1.isExpired = 1

          AND (SELECT COUNT(*) FROM Subscriptions s2

          WHERE s2.id_Publication = s1.id_Publication AND s2.id_Customer = s1.id_Customer AND s2.isPending = 1) = 0

          • 2. Re: SQL question
            jurgmay

            Genius!!! That's exactly what I'm after. I hadn't thought of the second select to count the pending subscriptions.

             

            Thanks so much!

             

            Juerg