Return value X where all rows that value fit criteria in other column-CodePudding

I need to return a list of numbers where all rows having the same number fit criteria in another column.

For example, all rows with same "num" must have non-null values in the Check-in column.

num| Check-in

 1| null
 1| X
 1| X
 2| X
 2| X
 3| X 
 3| X

Desired return: 2, 3

I know there has to be a simple way to do this without looping! Thank you!

CodePudding user response：

you can try grouping with a filter using having, here it compares the count of rows in each group with the count of rows per group that are not null, you only want those rows where the counts match.

select num
from t
group by num
having Count(*)=count(checkin);