michal.lisinski (4/2/2012)
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
Hi Mike,
I tried the following on your code:
;WITH [#Purchase] AS
(
SELECT 'A' ProductCode, 1 CustomerId UNION ALL
SELECT 'B' ProductCode, 1 CustomerId UNION ALL
SELECT 'A' ProductCode, 2 CustomerId UNION ALL
SELECT 'B' ProductCode, 3 CustomerId UNION ALL
SELECT 'A' ProductCode, 4 CustomerId UNION ALL
SELECT 'A' ProductCode, 4 CustomerId UNION ALL
SELECT 'A' ProductCode, 5 CustomerId UNION ALL
SELECT 'B' ProductCode, 5 CustomerId UNION ALL
SELECT 'C' ProductCode, 5 CustomerId
)
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)
Customer 1 has bought 'A' and 'B' and not 'C' and he gets found correctly
Customer 2 has bought 'A' and not 'B' and not 'C' and he gets found incorrectly
Customer 3 has bought not 'A' and 'B' and not 'C' and he gets found incorrectly
Customer 4 has bought two 'A' and not 'B' and not 'C' and he doesn't get found correctly
Customer 5 has bought 'A' and 'B' and 'C' and he doesn't get found correctly