CODEWITHSUNDEEP
sqlpostgresql
seriallchiller
seriallchiller

Reputation: 73

POSTGRESQL: how to filter to produce a query?

I have a text book question; List the ids and names of people who have received the grade an A for every class for the tables;

Student {studentID, name} Grade {studentID, courseName, grade}

and I am not sure how to filter out the grades so that i only show the A students. So far i have the solution

SELECT Student.studentID, Student.name
FROM Student, Grade
WHERE Student.studentID = Grade.StudentID AND
Grade.grade = 'A' AND
Grade.grade != 'B' AND
Grade.grade != 'C' AND
Grade.grade != 'D' AND
Grade.grade != 'F';

Is there a cleaner way I can filter this. It just seems a bit tedious that I write the not equals 4 times.

Upvotes: 0

Views: 129

Answers (2)

radar
radar

Reputation: 13425

you can use NOT EXISTS class. we are getting all students which didn't get A and filtering them out.

SELECT DISTINCT Student.studentID, Student.name
FROM  Student 
JOIN Grade 
ON Student.studentId = Grade.studentId
WHERE NOT EXISTS ( SELECT 1 FROM Grade G
                   WHERE G.grade !='A'
                   and G.studentID = Student.studentID
                  )

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

select studentid, s.name
from
    student s
    inner join
    grade g using (studentid)
group by studentid, s.name
having bool_and(g.grade = 'A')

Upvotes: 2

Related Questions