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)

  • April 2, 2012 at 6:31 am

    #1467586

    michal.lisinski (4/2/2012)

    Imho

    SELECT DISTINCT CustomerID

    FROM [#Purchase]

    WHERE (ProductCode IN ('A','B')) AND (CustomerID NOT IN

    (SELECT DISTINCT CustomerID

    FROM [#Purchase]

    WHERE (ProductCode = 'C')))

    GROUP BY CustomerID, ProductCode

    HAVING (COUNT(CustomerID) = 1)

    Regards

    Mike

    This also will select customers who have purchased just one of 'A' or 'B' and not purchased 'C'. the requirement was that they should have purchased both 'A' and 'B' and not purchased 'C'.