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 10:17 pm

    #1466153

    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')

    ;