CODEWITHSUNDEEP
phpmysqldatabasedatabase-administration
AntonB
AntonB

Reputation: 2853

MySQL query select students who do not have all 'A' grade

I have two tables, gradereport and student

The gradereport table has the studentNo which is a foreign key from the table student and it also has the grades for each student,

I must query the gradereport table to find out which students do not have all grade 'A' in the courses they took.

If they have any grades that are not 'A' then they should not be included in the results.

How can I query the table gradereport to find students who do not have all 'A's in their courses.

gradereport table

gradereport table

student table

student table

Upvotes: 0

Views: 2765

Answers (5)

CherryNerd
CherryNerd

Reputation: 1314

I'd do something like this:

SELECT DISTINCT StudentNo FROM gradereport GR WHERE NOT EXISTS ( SELECT 1 FROM gradereport GR2 WHERE GR.StudentNo = GR2.StudentNo AND GR2.Grade != 'A' )

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25842

you can do an exclusive check like so

SELECT distinct studentno FROM gradereport gr
WHERE gr.grade = "A"
AND NOT EXISTS
(   SELECT 1
    FROM gradereport
    WHERE grade <> "A"
    AND studentno = gr.studentno
)

DEMO

you could also do an exclusive join (my preference)

SELECT DISTINCT gr.studentno
FROM gradereport gr
LEFT JOIN gradereport gr1 ON gr1.studentno = gr.studentno AND gr1.grade <> "A"
WHERE gr1.studentno is null

BETTER DEMO

Upvotes: 1

steven
steven

Reputation: 4875

Find all students which have at least one grade A:

SELECT DISTINCT `StudentNo` FROM `gradereport` WHERE `Grade` = 'A'

Finding the oposite results:

SELECT `StudentNo` FROM `gradereport` WHERE `StudentNo` NOT IN (SELECT DISTINCT `StudentNo` FROM `gradereport` WHERE `Grade` = 'A')

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Students who do not have all A grade could be found as

select s.*,
gr.section,
gr.grade 
from student s 
join gradereport gr on gr.studentNo = s.studentnumber 
group by s.studentnumber,gr.section 
having sum(gr.grade='A') <> count(*) ;

Or if you dont care about the section then

select s.* 
from student s 
join gradereport gr on gr.studentNo = s.studentnumber 
group by s.studentnumber 
having sum(gr.grade='A') <> count(*)

Upvotes: 2

Ashley Clarke
Ashley Clarke

Reputation: 446

Try this:

SELECT student.name, student.number
FROM student, gradereport
WHERE student.number = gradereport.StudentNumber
AND gradereport.Grade != 'A'

Upvotes: 0

Related Questions