## Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

 Author Message Jeff Moden SSC Guru Group: General Forum Members Points: 432907 Visits: 43494 Comments posted to this topic are about the item Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle) --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs mark hutchinson SSC-Addicted Group: General Forum Members Points: 468 Visits: 446 @JeffDid you compare the speed of this query against one where your first (Group By) From clause is a Select Distinct (sub) query?Nice article. It builds nicely for the reader.I love seeing the Except clause being introduced to the reader who, like me, cut our teeth on SQL92 and need to learn newer language features to be more productive. Arjun S Old Hand Group: General Forum Members Points: 311 Visits: 236 Another way to get the result would be to use Intersect and then combine it with Except. The distinct part is handled implicitly.`--===== Find Customers that bought both "A" AND "B" SELECT CustomerID FROM #Purchase WHERE ProductCode IN ('A')INTERSECT SELECT CustomerID FROM #Purchase WHERE ProductCode IN ('B')EXCEPT--===== Find Customers that bought "C". SELECT CustomerID FROM #Purchase WHERE ProductCode IN ('C');` tommyh Hall of Fame Group: General Forum Members Points: 3710 Visits: 2000 Wouldnt something like this work just as well?`SELECT CustomerIdFROM #PurchaseWHERE ProductCode IN ('A','B', 'C')GROUP BY CustomerIDhaving sum(case when ProductCode = 'A' then 1 else 0 end) > 0 and sum(case when ProductCode = 'B' then 1 else 0 end) > 0 and sum(case when ProductCode = 'C' then 1 else 0 end) = 0`/T ColdCoffee SSCoach Group: General Forum Members Points: 17807 Visits: 5555 tommyh (3/28/2012)Wouldnt something like this work just as well?`SELECT CustomerIdFROM #PurchaseWHERE ProductCode IN ('A','B', 'C')GROUP BY CustomerIDhaving sum(case when ProductCode = 'A' then 1 else 0 end) > 0 and sum(case when ProductCode = 'B' then 1 else 0 end) > 0 and sum(case when ProductCode = 'C' then 1 else 0 end) = 0`/TYep, this is how i once implemented it! Cant remember the thread now.Here it is: http://www.sqlservercentral.com/Forums/FindPost1267224.aspxProved to elimate the EXCEPT part; also does a single scan on the table. Koen Verbeeck SSC Guru Group: General Forum Members Points: 125057 Visits: 13344 Nice spackle Jeff, and great alternatives in the discussion! How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?My blog at SQLKover.MCSE Business Intelligence - Microsoft Data Platform MVP ColdCoffee SSCoach Group: General Forum Members Points: 17807 Visits: 5555 OH yeah, Nice one Jeff. As always! Thanks a lot for taking time to teach us some of the tricks of the game. Thaks a lot Toby Harman SSCrazy Group: General Forum Members Points: 2059 Visits: 672 Had a play with this and was very impressed - nice job. I played around and added this index `CREATE INDEX IX_#Purchase_ProductCode ON #Purchase ( ProductCode) INCLUDE (CustomerID)`One concern I had was looking at the query plan, the EXCEPT does a clustered index scan. That may be a function of the small data set, but I am not sure.My preference for these types of queries has always been to do an OUTER JOIN on the one we don't want selected and then say that we only want the ones that didn't match returned.`SELECT a.CustomerID FROM #Purchase a LEFT OUTER JOIN #Purchase b ON b.CustomerID = a.CustomerID AND b.ProductCode IN ('C') WHERE a.ProductCode IN ('A','B') AND b.CustomerID IS NULL GROUP BY a.CustomerID HAVING COUNT(DISTINCT a.ProductCode) = 2`Any thoughts? James Dingle-651585 SSC Journeyman Group: General Forum Members Points: 86 Visits: 73 "Left Join Where Is Null" gets complicated to read/understand and is subject to cardinalities issues that will impact performance if the right set has many records for 1 record in the left hand side.There is also this way, probably the closest to natural language.`Select Distinct CustomerIDFrom #PurchaseWhere ProductCode = 'A'And CustomerID In( Select CustomerID From #Purchase Where ProductCode = 'B')And CustomerID Not In( Select CustomerID From #Purchase Where ProductCode = 'C')`I would be interested to have this table filled by 100,000+ rows of sample data and then compare execution plans and statistics of the proposed approaches.Comparing syntax without an idea of the performance signature is a bit useless IMHO.So here is another version of the initialization script from which the difference will be more blatant:`--===== 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(1,1), CustomerID INT, ProductCode VARCHAR(1000) PRIMARY KEY CLUSTERED (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' UNION ALL SELECT 3, 'D' UNION 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 SELECT 5, 'D' UNION ALL------- Customer #6 doesn't meet the criteria. -- Bought more than 1 of '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 of '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'godeclare @i int = 10;while @i < 100000begin insert into #Purchase (CustomerID, ProductCode) select CustomerID + @i, ProductCode + Left(ProductCode, 1) From #Purchase set @i = @i * 2end`Then turn on statistics or open your SQL profiler`SET STATISTICS IO ON`And now you're ready.Then you will also need an index on ProductCode.`Create Index IX_ProductCode On #Purchase (ProductCode) Include (CustomerID);` chintan.j.gandhi SSC Journeyman Group: General Forum Members Points: 99 Visits: 52 How about this? Although i agree query might get longer when there would be more than 3 items` SELECT DISTINCT CustomerID FROM #Purchase P WHERE EXISTS (SELECT 1 FROM #Purchase P1 WHERE P.customerid = P1.customerid AND P1.productcode = 'A' ) AND EXISTS (SELECT 1 FROM #Purchase P2 WHERE P.customerid = P2.customerid AND P2.productcode = 'B' ) AND NOT EXISTS (SELECT 1 FROM #Purchase P3 WHERE P.customerid = P3.customerid AND P3.productcode = 'C' )`