1 2 3 Previous Next 38 Replies Latest reply on Mar 17, 2015 5:56 AM by siplus

    SQL: How to pull a list of customers who are not subscribed to a magazine?

    jurgmay

      Hello!

       

      I have two tables: Customers and Subscriptions.

       

      The important fields are 'id' in the Customers table and 'id', 'id_Customer' and 'publicationName' in the Subscriptions table.

       

      The join is Customer::id = Subscriptions::id_Customer

       

      I am trying to retrieve a list of id's for every customer who has not subscribed to a magazine. An example of the Subscriptions table would look like this:

       

      idid_CustomerpublicationName
      11Magazine 1
      21Magazine 3
      32Magazine 1
      43Magazine 1
      53Magazine 2
      63Magazine 3
      74Magazine 3
      85Magazine 2

       

      If I wanted a list of customers who have NOT subscribed to 'Magazine 2' I would expect a list as follows:

       

      Customer id
      1
      2
      4

       

      Ideally I would like this as an SQL query  as this would form part of a small library of other SQL queries which I am using.

       

      Does anyone have any idea how to approach this? I've tried:

       

      ExecuteSQL ( "

      SELECT Customers.id

      FROM Customers

      LEFT OUTER JOIN Subscriptions

      ON Customers.id = Subscriptions.id_customer

      WHERE Subscriptions.publicationName NOT LIKE 'Magazine 2'

      " ; "   " ; "" )

       

      but this gives lists the customer id more than once if they have subscribed to more than one magazine and none of them are 'Magazine 2' and the id list does not include all of the records in the Customers table (probably because of the WHERE clause).

       

      Does anyone have any idea how to approach this?

       

      Many thanks,

       

      Juerg

        1 2 3 Previous Next