SQL Query - Help Needed

[djridge100]

Hi,

I have a table called Customer_Subscriptions (sample data for this table is shown below).

Customers_ID Journals_ID
M31987 EJTH
M31987 ETHM
M31987 EVAN
M42546 SCCB
M42546 TRAN

I have the following SQL query to show journals that a customer subscribes to:

SELECT Journals_ID
FROM Customer_Subscriptions
WHERE Customers_ID="M31987" AND Journals_ID In ("BARR","CUEN","TRAN","SCCB","ETHM","EVRT","EVQU","EJTH","EVAN");

This would return:

Journals_ID
EJTH
ETHM
EVAN

How would I write a query to return the opposite, e.g the journals they don't subscribe to:

Journals_ID
BARR
CUEN
TRAN
SCCB
EVRT
EVQU

Thanks in advance,
Richard.

 

[Rabbie]

Try

SELECT Journals_ID
FROM Customer_Subscriptions
WHERE Customers_ID="M31987" AND NOT Journals_ID In ("BARR","CUEN","TRAN","SCCB","ETHM","EVRT","EVQU", "EJTH","EVAN");

 

[djridge100]

Thanks for the very quick reply!

However, I've tried what you suggested and the query doesn't return anything.

Any ideas Rabbie, or any one else?

Thanks,
Richard.

 

[Rabbie]

I need some more background info. Do you have a table of Journal IDs? because having given this more thought we need to find the Journal IDs which are not returned by "WHERE Customers_ID="M31987" AND Journals_ID In ("BARR","CUEN","TRAN","SCCB","ETHM","EVRT","EVQU", "EJTH","EVAN");"

 

[djridge100]

Hi Rabbie,

Not to worry, I've just managed to solve it in another way. What I've done is create a new table that just holds the 9 journal codes. I then created a query that shows the journals the customer is subscribed to. I then used the 'unmatched query' wizard to compare the results of the query with the codes in the table. This returned the codes of journals not subscribed to.

Many thanks for your help though,
Richard.