>Hi all,
>Is it possible to produce a single select statement to achieve the following:-
>
>If you have a Customers table (Id, Name), an Items table (Id, Name) and a Subscriptions table (CustomerID, ItemId). The Subscriptions table will hold a record for each Item that a Customer subscribes too.
>
>Is it then possible to get all the Customers who subscribe to a specified list of Items and NO others.
>
>ie If this is a paper round I want to find all those customers who take the Times and the Sunday Times and NO other papers.
>
>Its the NO other papers bit that has me stumped at the moment. Any help greatly appreciated.
>Caroline
Just to get the subscriptions:
SELECT * ;
FROM Subscriptions;
WHERE INLIST(ItemId, ID_SUNDAYTIMES, ID_TIMES);
AND CustomerId NOT IN (SELECT CustomerId FROM Subscriptions WHERE !INLIST(ItemId, ID_SUNDAYTIMES, ID_TIMES))
Any good?
Kev