Return a Value When All Child Values are != 1

Question

I'm trying to query a table called pollerstatus to return the nodeid's where the rowstatus != 1 for all of the instances of that nodeid. In the table below I want the SQL query to return nodeid 1012 because that's the only one where all of the rowstatus values !=1.

rowid	nodeid	label	rowstatus
1	1011	LC1	1
2	1011	LC1	1
3	1012	LC2	0
4	1012	LC2	3
5	1012	LC2	0
6	1013	LC3	1
7	1013	LC3	1
8	1014	LC4	4
9	1014	LC4	5
10	1014	LC4	1

The best I've managed to come up with is a count for each nodeid where rowstatus != 1

SELECT COUNT (concat(NodeID, rowstatus)) AS row_count, NodeID
FROM pollerstatus
WHERE rowstatus != 1
group by NodeID

This returns

row_count	nodeid
2	1012
2	1014

But this is no good because as well as returning nodeid 1012 which does have all rowstatus != 1 it also returns nodeid 1014 which doesn't have all rowstatus != 1

Answer 1

You can do a conditional count:

SELECT NodeID
FROM pollerstatus
GROUP BY NodeID
HAVING COUNT(CASE WHEN rowstatus = 1 THEN 1 END) = 0;

This returns no rows because you have rowstatus = 1 on every Node :)