Query doubt (grouping)

Navigation
3

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

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

    
asked by anonymous 10.10.2015 / 20:09

1 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 subjects, 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 the RAs that have the number of unique / distinct disciplines with concept = 'R' equal to 5.

See SQLFiddle

    
10.10.2015 / 21:26
State Machine with AngularJS Method ignoring the callback function in a jQuery plugin