Search code examples
postgresqlsqlalchemypostgresql-9.3

Postgresql: filter query to hide all from column a if any of column b has specific value

I am new to postgresql/ sqlalchemy and I had a query question. I have the following join table 

select classroom.name, student.class_name, student.full_name
from classroom
inner join student on classroom.name = student.class_name

Which returns the following:

classroom.name | student.class_name | student.full_name
-------------------------------------------------------
Math           |        Math        |        Joe
Math           |        Math        |        Kim
Math           |        Math        |       Chris
English        |       English      |        Joe
English        |       English      |        Kim

What I am trying to do is filter this query so that if a certain student, lets just Chris, exists in the student.name column, it wouldnt return any row with classroom.name = Math since one of the entries has Chris. So the output that I am looking for is 

classroom.name | student.class_name | student.full_name
-------------------------------------------------------
English        |       English      |        Joe
English        |       English      |        Kim

I've tried adding a Where student.full_name = 'Chris', but that only hid the one entry and still left the other two entries.

Any help would be appreciated, thank you!

Solution

  • Add a WHERE clause that excludes the rows you don't want:

    ...
WHERE NOT EXISTS (
   SELECT 1
   FROM classroom c
      JOIN student s ON c.name = s.class_name
   WHERE c.name = classroom.name
     AND s.full_name = 'Chris'
)

    This answer is provided under the Creative Commons 2.0 license.
    You have to give me credits in your homework.