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:
If I wanted a list of customers who have NOT subscribed to 'Magazine 2' I would expect a list as follows:
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 ( "
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?