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.

sgeddes · Accepted Answer · 2016-08-15 20:43:30Z

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'
    )

answered Aug 15, 2016 at 20:43

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2016-08-15 20:47:52Z

0

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');

edited Aug 15, 2016 at 20:47

answered Aug 15, 2016 at 20:45

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Query Subquery Results in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related