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 7:15 am

    #1467608

    Jonathan AC Roberts (4/2/2012)

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

    Hmm, r u sure? Customer no 6 took product A and no B, customer 7 took product B and no A, both didn't take C, but query doesn't return them.

    Regards

    Mike