AnsweredAssumed Answered

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

Question asked by jurgmay on Mar 2, 2015
Latest reply on Mar 17, 2015 by siplus

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

Outcomes