I would like to Group by House and check if that group contain listed items ‘Sink, ‘Faucet’, ‘Toilet’ in Fixture column, if yes then check if contain all listed items ‘Sink, ‘Faucet’, ‘Toilet’, if not list all missing ones by House.

Table1
House Fixture
1 Sink
1 Faucet
1 Toilet
2 Sink
2 Faucet
3 Sink

I’m new to SQL and so far I’ve tried the following queries, unfortunately without good results because it’s not exactly what I need.

SELECT House,
CASE
WHEN COUNT(CASE WHEN Fixture = ‘Sink’ THEN 1 END) = 0 THEN ‘Sink’
WHEN COUNT(CASE WHEN Fixture = ‘Faucet’ THEN 1 END) = 0 THEN ‘Faucet’
ELSE ‘None’
END AS MissingFixture
FROM Fixtures
GROUP BY House
HAVING COUNT(CASE WHEN Fixture IN (‘Sink’, ‘Faucet’) THEN 1 END) < 2

SELECT House,
CASE
WHEN NOT EXISTS (SELECT 1 FROM Fixtures f1 WHERE f1.House = f.House AND f1.Fixture = ‘Sink’) THEN ‘Sink’
WHEN NOT EXISTS (SELECT 1 FROM Fixtures f2 WHERE f2.House = f.House AND f2.Fixture = ‘Faucet’) THEN ‘Faucet’
END AS MissingFixture
FROM Fixtures f
GROUP BY House
HAVING COUNT(CASE WHEN Fixture IN (‘Sink’, ‘Faucet’) THEN 1 END) < 2;