MySQL Query: how to select rows that do not have a specific value?

Question

I am having trouble recording the request and I don’t even know if this 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 

The above table is just an example. There are many more columns in a real table, and that means such a tree. I need a way to select only group names that do not have an active string. In this case, the groups “A” and “B” have at least one line with “active” = “YES”, but if you look at C, there are no active lines. The only thing I will need as a result is the value of the group column (in this case, “C”) is not an entire row.

Is it possible?

Answer 1

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

Answer 2

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

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