Query Subquery Results in SQL

Question

I am relatively new to SQL and I may be over thinking it but given the following table.I want a list of all students that are enrolled in Chemistry but not Mathematics.So given the data I would have Student 1.

    Student_ID  Subject
      1      Chemistry
      2      Mathematics
      2      Chemistry
      3      History

Here's what I tried

SELECT        Student_ID
FROM          Student 
WHERE        (Subject = 'Chemistry') AND (Subject <> 'Mathematics')

Perhaps I need to group somewhere because rows and returned that exist for both criteria.

Answer 1

You can use where and not in

select Student_ID
from Student 
where Subject  = 'Chemistry'
and Student_ID NOT IN  ( select Student_ID from Student where subject ='Mathematics');

Answer 2

Here's one option using conditional aggregation:

select student_id
from student
group by student_id
having max(case when subject = 'Chemistry' then 1 end) = 1 and
       max(case when subject = 'Mathematics' then 1 else 0 end) != 1

---

And here's another option using not exists:

select student_id
from student s
where subject = 'Chemistry' and not exists (
    select 1
    from student s2
    where s.student_id = s2.student_id and st.subject = 'Mathematics'
    )