Question about query (grouping)

Question

I have a table called ALUNO_LEGADO in SQL Server DBMS as below:

Assuming there are only 5 disciplines (1,2,3,4,5) and the concepts are A = Aprovado and R = Reprovado, what condition I use to search for only the RA'which have failed to all the 5 disciplines?

Answer 1

Assuming the following structure

create table aluno_legado
( 
   ra          varchar(03),
   disciplina  int,
   conceito    varchar(01)
);

insert into aluno_legado(ra, disciplina, conceito) values
('ra1', 1, 'R'),
('ra1', 2, 'R'),
('ra1', 3, 'R'),
('ra1', 4, 'R'),
('ra1', 5, 'R'),
('ra2', 1, 'R'),
('ra2', 2, 'R'),
('ra2', 3, 'R'),
('ra2', 4, 'R'),
('ra2', 5, 'A'),
('ra3', 1, 'R'),
('ra3', 2, 'R'),
('ra3', 3, 'R'),
('ra3', 4, 'R');

Assuming there are only 5 disciplines, the following query will produce the result you want

select ra
from   aluno_legado
group by ra
having count(distinct case when conceito = 'R' then disciplina end) = 5;

This query returns RA that have the number of unique/distinct disciplines with concept = 'R' equal to 5.

See the Sqlfiddle