Select rows based on condition on group

Question

I have a following table, with values of code can be from 1 to 10. I want to select the product only if it has value of code greater than or equals to 3 & it should not have code = 9 assigned.

ProductId   Code
844558      3
844558      3.5
844558      4
296013      2
296013      3
494948      3.5
494948      4
494948      9
392991      4
392991      9.5

Result will have only Product Id 844558 & 392991. 296013 is not selected because it has code 2 assigned 494948 is not selected because it has code 9

I wrote below query to select records with code greater than or equals 3. How to exclude products with code = 9

Declare @Product Table(ProductId INT, Code DECIMAL(10,2))

INSERT INTO @Product(ProductId, Code)
Select 844558,3
UNION
Select 844558,3.5
UNION
Select 844558,4
UNION
Select 296013,2
UNION
Select 296013,3
UNION
Select 494948,3.5
UNION
Select 494948,4
UNION
Select 494948,9
UNION
Select 392991,4
UNION
Select 392991,9.5 


SELECT ProductId
FROM @Product
WHERE Code <> 9
GROUP BY ProductId
HAVING MIN(Code) >= 3

Answer 1

Enhance your having clause:

SELECT ProductId
FROM @Product
GROUP BY ProductId
HAVING MIN(Code) >= 3 AND
       SUM(CASE WHEN Code = 9 THEN 1 ELSE 0 END) = 0

Answer 2

Exclude the product at source and this should return the correct result set. I have done this using a where clause which selects all of the productid values where code is 9. That way the outer result set is all codes greater than 3 and the inner result set excludes anything that has a code of 9.

Declare @Product Table(ProductId INT, Code DECIMAL(10,2))

INSERT INTO @Product(ProductId, Code)
Select 844558,3
UNION
Select 844558,3.5
UNION
Select 844558,4
UNION
Select 296013,2
UNION
Select 296013,3
UNION
Select 494948,3.5
UNION
Select 494948,4
UNION
Select 494948,9
UNION
Select 392991,4
UNION
Select 392991,9.5 


SELECT ProductId
FROM @Product
WHERE ProductID not in (select ProductID from @product where Code=9)
GROUP BY ProductId
HAVING MIN(Code) >= 3;