Rows with multiple corresponding values in the same table

Question

Suppose I have a table

Name          Wear
Martin        Hat
Martin        ?
Martin        Shirt
Alfred        Tee
Alfred        Jeans

And I only want names of people whose Wear value is fully given, that is without a NULL (?) value in there.

I thought about using a group by ... having Wear <> NULL but this isn't sufficient, as there can be more than one Wear value.

In this case, I would like to only return 'Alfred'.

Answer 1

You can do it like

HAVING SUM(Wear IS NULL) = 0

or

HAVING COUNT(*) = COUNT(Wear)

Answer 2

Use correlated subquery

select distinct name from tablename a where not exists 
    (select 1 from tablename b where a.name=b.name and wear is null)