CODEWITHSUNDEEP
sqlpostgresql
Sean
Sean

Reputation: 3

SQL query to select records that have one value in a column but missing another value

I have a database that has two tables. One holds the name of the classes a student can take (class_id, class_desc) and the other has the students information (id, fname, lname, class_id). I join the tables to get a roster of who is taking what class by joining on the class_id. How do I go about getting a roster of students that are taking class 'cl_2055' but not taking class 'cl_6910'?

Upvotes: 0

Views: 786

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I like to use aggregation for this purpose

select si.fname, si.lname
from studentinformation si
where ci.classid in ('cl_2055', 'cl_6910')
group by si.fname, si.lname
having min(ci.classid) = 'cl_2055' and max(ci.classid) = 'cl_2055';

First, this assumes that the class identifiers are the ids and not the description (seems logical to me). If they are the descriptions, then you need to join in the class information.

How does this work? The where clause filters down to students who might be in both classes. The group by aggregates to a single row per student. And the having keeps students who are only in "cl_055".

Upvotes: 1

Alan
Alan

Reputation: 1428

You really need to change your schema if you can. You should have three tables. Student, Class and StudentClass, (StudentClass contains a pointer to each of the other tables.

But if you insist on using the schema you have...

SELECT
    *
FROM
    students 
WHERE
    class_id = 'cl_2055'
    AND id NOT IN (SELECT id FROM students where class_id = 'cl_6910')

This assumes the id is not unique, and you are using ID to represent students. If you are using ID to represent records, then you will need the second approach:

SELECT
    *
FROM
    students students_in_2055
WHERE
    class_id = 'cl_2055'
    AND NOT EXISTS (
        SELECT 1 
        FROM students students_in_6910 
        WHERE students_in_6910.class_id = 'cl_6910' 
           AND students_in_2055.fname = students_in_6910.fname
           AND students_in_2055.lname = students_in_6910.lname
    )

Upvotes: 1

Related Questions