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

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

Answer by Tim Biegeleisen for T-SQL select if person has product A but not both A and B

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

Answer by Felypp Oliveira for T-SQL select if person has product A but not both A and B

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

Answer by Paparazzi for T-SQL select if person has product A but not both A and B

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'  

Answer by Pinwar13 for T-SQL select if person has product A but not both A and B

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  

Answer by Joni for T-SQL select if person has product A but not both A and B

Thanks for all the great answers :)

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72