Products Without Duplicates

Time for a new puzzle.

Given the below products table, return a result set of all Product Codes that are not associated with a Product Type that has multiple entries.

Product TypeProduct Code
Alpha01
Alpha02
Bravo03
Bravo04
Charlie02
Delta01
EchoEE
FoxtrotEE
GulfGG

The result set will contain the Product Codes of EE and GG as Product Codes 01, 02, 03 and 04 are associated to Alpha and Bravo, which have multiple entries.

Here is my answer to the puzzle:

DROP TABLE IF EXISTS #Products;
GO

CREATE TABLE #Products
(
Product  VARCHAR(10),
ProductCode VARCHAR(2),
PRIMARY KEY (Product, ProductCode)
);
GO

INSERT INTO #Products VALUES
('Alpha','01'),('Alpha','02'),
('Bravo','03'),('Bravo','04'),
('Charlie','02'),('Delta','01'),
('Echo','EE'),('Foxtrot','EE'),
('Gulf','GG');
GO

WITH cte_Dups AS
(
SELECT Product
FROM   #Products
GROUP BY Product
HAVING COUNT(*) >= 2
),
cte_ProductCodes AS
(
SELECT  ProductCode
FROM    #Products
WHERE   Product IN (SELECT Product FROM cte_Dups)
)
SELECT  DISTINCT ProductCode
FROM    #Products
WHERE   ProductCode NOT IN (SELECT ProductCode FROM cte_ProductCodes);
GO

Leave a Reply