Query Doubt - Group Data

Navigation
1

I have a question about how to group the data as follows.

I have a table named ALUNO_LEGADO , as below:

I need to assemble a query to catch all the students who failed in all the subjects enrolled in a given year. Eg: Select students who failed in all subjects taken in 2015.

  • Note: the concepts are: A = Approved, R = Disapproved
  • Obs. 2: There are not a number of standard courses for each student, as they have the free choice of enrolling in 1 or more courses.

The idea is to select the face that failed in all subjects in which it was related in the year 2015. 

create table aluno_legado
SELECT 
RA

FROM ALUNO_LEGADO

WHERE conceito = 'R'
AND ano = 2015

GROUP BY RA

However this query simply brings RA's that has concept R in 2015, and not only the students who failed in all subjects taken in 2015. Do you understand?

    
asked by anonymous 12.10.2015 / 17:48

1 answer

1

Using not exists

In addition to grouping by RA , you should deny if the student has passed a subject. 

select nome, ra, ano
  from aluno_legado al
 where ano = 2015
   and not exists(select 1
                    from aluno_legado e
                   where e.ra=al.ra
                     and e.ano=al.ano 
                     and e.conceito='A')
 group by ra, nome, ano

See more details on this fiddle .

Explaining: If the sub-select that searches for subjects in which the student has been approved returns some line the not exists clause will fail.

Using having

Another alternative assuming that the conceito column can only have the A and R values would be with the having clause using the min function. 

select nome, ra, ano
  from aluno_legado al
 where ano = 2015
 group by ra, nome, ano
having min(conceito) = 'R'

See more in this fiddle .

Explaining: Since A is less than R if the student was approved in any subject the min function will return A causing the comparison to fail.

I've put the name column just for easy viewing.

    
12.10.2015 / 19:47
Catch AD user asp net mvc Problem with IF / ELSE and variables