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 Type | Product Code |
|---|---|
| Alpha | 01 |
| Alpha | 02 |
| Bravo | 03 |
| Bravo | 04 |
| Charlie | 02 |
| Delta | 01 |
| Echo | EE |
| Foxtrot | EE |
| Gulf | GG |
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