SQL query customers bought a and b, but did not buy C

See, feel very useful to record down

Original source

The problem description is simple, which is to query customers who have purchased products A and b but have not purchased product C

Test data

--===== conditionally drop the test table to make – reruns in SSMS easier. IF object_id (' tempdb..         #Purchase ', ' U ') is not NULL DROP TABLE #Purchase;--===== Create the Test table CREATE table #Purchase ( Purchaseid int IDENTITY (+), CustomerID int, ProductCode CHAR (1) PRIMARY KEY clust Ered (Purchaseid));--===== Populate the test table with known data.     INSERT into #Purchase (CustomerID, ProductCode)-------Customer #1 precisely meets the criteria. --bought ' A ' and ' B ' but not ' C '.     Select 1, ' A ' union ALL SELECT 1, ' B ' union all-------Customer #2 also meets the criteria. --bought ' A ' and ' B ' and somthing else,--but not ' C '.     Select 2, ' A ' union ALL Select 2, ' B ' union ALL Select 2, ' D ' union all-------Customer #3 also meets the criteria. --bought ' A ' and ' B ' and something else,--but not ' C '. Select 3, ' A ' union ALL select 3, ' B ' union ALL select 3, ' D ' union ALL select 3, ' a ' unionAll SELECT 3, ' D ' Unions all-------Customer #4 doesn ' t meet the criteria. --bought ' A ' and ' B ' but also bought ' C '.     Select 4, ' A ' union ALL Select 4, ' B ' union ALL Select 4, ' C ' union all-------Customer #5 doesn ' t meet the criteria. --bought ' A ' and ' B ' and something else,--but also bought ' C '. Select 5, ' A ' union ALL select 5, ' B ' union ALL Select 5, ' A ' union ALL select 5, ' B ' union ALL Select 5, ' C ' union ALL SE     Lect 5, ' D ' UNION all-------Customer #6 doesn ' t meet the criteria. --bought more than 1 of the ' A ' and something else--but not ' B '.  Select 6, ' A ' union ALL select 6, ' A ' union ALL select 6, ' D ' union ALL Select 6, ' E ' union all-------Customer #7 doesn ' t     Meet the criteria. --bought more than 1 's ' B ' and something else--but not ' A '. Select 7, ' B ' union ALL select 7, ' B ' union ALL Select 7, ' D ' union ALL Select 7, ' E ';

Skip through some simple procedures in the middle. In the test data the customer 6 bought two A but did not buy B, the customer 7 bought two B but did not buy an A. They should not appear at the same time to purchase a and B customer focus

Query statements

--===== Find Customers that bought "a" and "B"     --has a WHERE for GROUP BYs. SELECT CustomerID from   #Purchase  WHERE ProductCode in (' A ', ' B ') the  GROUP by CustomerID has COUNT (DISTINCT ProductCode) = 2;

Query results

CustomerID-----------12345

Now, from the results above, we've eliminated customers who bought C.

   From #Purchase  WHERE ProductCode in (' A ', ' B ')  GROUP by CustomerID have COUNT (DISTINCT ProductCode) = 2 except--= = = = = Find Customers that bought "C". SELECT CustomerID from   #Purchase  WHERE ProductCode in (' C ');

Query results

CustomerID-----------123

Get