Have a look at example:
DECLARE @tmp TABLE (ingredientid INT, nvalue INT)
INSERT INTO @tmp (ingredientid, nvalue)
VALUES (1, 9), (1, 0),
(1, 5), (2, 0),
(2, 0), (3, 4),
(3, 5), (3, 6),
(4, 0), (4, 0)
SELECT DISTINCT ingredientid
FROM @tmp
WHERE nvalue = 0
returns:
1
2
4
SELECT T.ingredientid
FROM (
SELECT ingredientid, COUNT(ingredientid) AS CountOfIid
FROM @tmp
GROUP BY ingredientid
) AS T INNER JOIN @tmp AS D ON T.ingredientid = D.ingredientid
WHERE D.nvalue = 0
GROUP BY T.ingredientid, T.CountOfIid
HAVING COUNT(T.ingredientid) = T.CountOfIid
returns:
2
4