Query to find group that has no representative value in that group
I'm trying to come up with a query that will allow me to identify groups of rows that do not contain a row with a certain value. A simple example might be a class table and student table as follows:
create table class (class_id varchar2(2));
create table student (student_id number(38),class_id varchar2(2),a_student varchar2(3));
with rows:
insert into class values ('A');
insert into class values ('B');
insert into student values (1,'A','yes');
insert into student values (2,'A','no');
insert into student values (3,'B','no');
insert into student values (4,'B','no');
I would like to find which class does not have a straight a student in it. The result should look like this: