Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Jeff Moden Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle) RE: Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

  • March 28, 2012 at 11:04 pm

    #1466162

    tommyh (3/28/2012)

    Wouldnt something like this work just as well?

    SELECT

    CustomerId

    FROM #Purchase

    WHERE ProductCode IN ('A','B', 'C')

    GROUP BY CustomerID

    having 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

    Yep, this is how i once implemented it! Cant remember the thread now.

    Here it is: http://www.sqlservercentral.com/Forums/FindPost1267224.aspx

    Proved to elimate the EXCEPT part; also does a single scan on the table.