MySQL Query: How to select rows that don't have a certain value?

Question

I am having trouble writing a query and I don't even know if it is possible. Take this table for example:

id   group  active  

1    A      NO  
2    A      YES  
3    A      NO  

4    B      YES  
5    B      NO  

6    C      NO  
7    C      NO  

Table above is just an example. In real table there are much more columns the those tree so have that in mind. What I need is a way to select only group names that don't have any active row. In this case both "A" and "B" groups have at least one row with "active" = "YES" but if you look at C there are no active rows. The only thing I would need as a result is a group column value (in this case "C") not entire row.

Is this possible?

Answer 1

SELECT DISTINCT group FROM table WHERE group NOT IN
    (SELECT DISTINCT group FROM table WHERE active = 'YES')

Answer 2

You first want to get all the groups you wish to exclude, and then use the NOT IN clause to return all the other groups not in that list.

SELECT DISTINCT t.group 
FROM table t
WHERE t.group NOT IN 
    (SELECT DISTINCT t.group 
     FROM table t 
     WHERE t.active='YES');