CODEWITHSUNDEEP
sql-servert-sql
Joni
Joni

Reputation: 19

T-SQL select if person has product A but not both A and B

I'm unsure how to achieve this. I would like to get persons that have "ProductA" but not those who have both "ProductA" and "ProductB".

Expected output:

CustomerID | Product
--------------------
         1 | ProductA
         2 | ProductA
         3 | ProductA
         6 | ProductA
         7 | ProductA

Table:

CREATE TABLE #TempTable 
(
     CustomerID INT,
     Product    VARCHAR(50)
)

INSERT INTO #TempTable (CustomerID, Product)
VALUES
  ('1', 'ProductA'),
  ('2', 'ProductA'),
  ('3', 'ProductA'),
  ('4', 'ProductA'),
  ('4', 'ProductB'),
  ('6', 'ProductA'),
  ('7', 'ProductA');

Upvotes: 1

Views: 164

Answers (4)

Pintu Kawar
Pintu Kawar

Reputation: 2156

Using PIVOT:

WITH CTE
AS
(
    Select CustomerID, [ProductA], [ProductB] from #TempTable
    PIVOT
    (
    MAX(Product) FOR Product IN ([ProductA], [ProductB])
    ) p
    WHERE [ProductB] IS NULL
)
Select CustomerID, [ProductA] as Product
from CTE

Upvotes: 0

Felypp Oliveira
Felypp Oliveira

Reputation: 2197

Grouping by CustomerID you can check if the minimum and maximum Product available is the same, which should be the one you're looking for, actually, 'ProductA':

SELECT 
  CustomerID,
  min(Product)
FROM TempTable
GROUP BY CustomerID
HAVING 1=1
  AND min(Product) = 'ProductA'
  AND max(Product) = 'ProductA'
;

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

SELECT C1.CustomerID
FROM #TempTable C1 
LEFT JOIN #TempTable C2  
ON C2.ID = C1.ID 
AND C2.Product = 'ProductB'
where C2.ID is null
AND C1.Product = 'ProductA'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522376

I would use conditional aggregation here on each customer and just check that a customer bought A at least once but not B.

SELECT CustomerID
FROM #TempTable
GROUP BY CustomerID
HAVING SUM(CASE WHEN Product = 'ProductA' THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN Product = 'ProductB' THEN 1 ELSE 0 END) = 0

Upvotes: 2

Related Questions