Finding value from a third column based on specific row criteria of 2 other columns

EDITED ON March 6, 2023. Added more information. Also providing the solution based on feedback from people on this thread. Thanks!!

In the example provided below, based on conditions that will filter out rows on two columns, I want to filter them out again to get the CacheUID (third column)

The code below will allow you to recreate the following resultset:

Based on different combination of rows (per FilterCriteriaName and CriteriaValue), I need to retrieve what is the corresponding CacheUID. The FilterCriteriaName and CriteriaValue corresponds to filter information. Here are two examples:

Scenario 1:
Want to retrieve the correct CacheUID (and only 1) when filter is based on Product 4 AND Product 6.The combination of Product 4 and 6 can be found in CacheUID 2, 3 and 5. But ONLY CacheUID 2 contains ONLY these criteria.

Scenario 2:
Want to retrieve the correct CacheUID (and only 1) when filter is based on Product 4 AND Product 6 AND Equipment 6.Product 4 can be found in CacheUID 1, 2, 3 and 5. Product 6 can be found in CacheUID 2, 3, 4 and 5. Equipment 6 can be found in CacheUID 5, But, the combination of Product 4 and 6 and Equipment 6 can be found only in CacheUID 5.

And here are the result I need for each scenario

Scenario 1:

Scenario 2:

Run the query below that will create the right data to cover the scenarios listed and now contains the solution in the last two SELECT.

CREATE TABLE #TestTable
(
	CacheUID [bigint] NOT NULL,
	FilterCriteriaName VARCHAR(50) NOT NULL,
	CriteriaValue [bigint] NOT NULL,
	UID [bigint] IDENTITY(1,1) NOT NULL
)

INSERT	#TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES	('Product', 1, 4)

INSERT	#TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES	('Product', 2, 4)

INSERT	#TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES	('Product', 2, 6)

INSERT	#TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES	('Product', 3, 4)

INSERT	#TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES	('Product', 3, 6)

INSERT	#TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES	('Product', 3, 8)

INSERT	#TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES	('Equipment', 4, 16)

INSERT	#TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES	('Equipment', 4, 3)

INSERT	#TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES	('Equipment', 5, 4)

INSERT	#TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES	('Equipment', 5, 6)

INSERT	#TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES	('Product', 5, 6)

INSERT	#TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES	('Product', 5, 4)

SELECT	CacheUID, FilterCriteriaName, CriteriaValue
FROM	#TestTable

-----------------------------------------------
-- Scenario 1: Filter on two products (4, 6) --
-----------------------------------------------

SELECT		'Scenario 1 - Only 4 or 6', CacheUID
FROM		#TestTable
GROUP BY	CacheUID
HAVING		COUNT(CASE WHEN FilterCriteriaName = 'Product' AND CriteriaValue = 4 THEN 1 END) > 0 AND			-- Product 4 present
			COUNT(CASE WHEN FilterCriteriaName = 'Product' AND CriteriaValue = 6 THEN 1 END) > 0 AND			-- Prodct 6 present
			COUNT(CASE WHEN FilterCriteriaName = 'Product' AND CriteriaValue NOT IN (4, 6) THEN 1 END) = 0 AND  -- Only product 4 or 6
			COUNT(CASE WHEN FilterCriteriaName = 'Equipment' AND CriteriaValue > 0 THEN 0 END) = 0

---------------------------------------------------------------------
-- Scenario 2: Filter on two products (4, 6) and one equipment (6) --
---------------------------------------------------------------------

SELECT		'Scenario 2 - Only Product 4, 6 Present + Equipment 6', CacheUID
FROM		#TestTable
GROUP BY	CacheUID
HAVING		COUNT(CASE WHEN FilterCriteriaName = 'Product' AND CriteriaValue = 4 THEN 1 END) > 0 AND			-- Product 4 present
			COUNT(CASE WHEN FilterCriteriaName = 'Product' AND CriteriaValue = 6 THEN 1 END) > 0 AND			-- Product 6 present
			COUNT(CASE WHEN FilterCriteriaName = 'Product' AND CriteriaValue NOT IN (4, 6) THEN 1 END) = 0 AND  -- Only Product 4 or 6
			Count(CASE WHEN FilterCriteriaName = 'Equipment' AND CriteriaValue = 6 THEN 1 END) = 1				-- Equipment 6 present


DROP TABLE #TestTable

Thanks!

Your query is almost correct, and can be made to work by adding a third assertion in the HAVING clause which excludes any criteria value which is not 4 or 6:

<!-- language: sql -->

SELECT CacheUID
FROM #TestTable
WHERE FilterCriteriaName = 'Product'
GROUP BY CacheUID
HAVING COUNT(CASE WHEN CriteriaValue = 4 THEN 1 END) > 0 AND         -- 4 present
       COUNT(CASE WHEN CriteriaValue = 6 THEN 1 END) > 0 AND         -- 6 present
       COUNT(CASE WHEN CriteriaValue NOT IN (4, 6) THEN 1 END) = 0;  -- only 4 or 6