CODEWITHSUNDEEP
sqlsubquery
Bell
Bell

Reputation: 7

SQL sub-queries not returning expected result

Select name 
from instructor 
where dept_name = 'Comp. Sci.' and ID in
    (select ID
     from course 
     where title != 'Intro. to Computer Science')

With this input I want the output to be the instructors who never taught "Intro. to Computer Science but the output is all instructors from CS rather than just the 1 Professor Brant who has not taught Intro to Computer Science. What am I doing wrong?

instructor

course

teaches

Upvotes: 0

Views: 100

Answers (2)

forpas
forpas

Reputation: 164194

Use NOT EXISTS:

select i.name 
from instructor i
where i.dept_name = 'Comp. Sci.' 
and not exists (
  select 1 
  from teaches t inner join course c
  on c.course_id = t.course_id 
  where t.id = i.id and c.title = 'Intro. to Computer Science'
)

or group by instructor and set the condition in the HAVING clause:

select i.name 
from instructor i
inner join teaches t on t.id = i.id
inner join course c on c.course_id = t.course_id 
where i.dept_name = 'Comp. Sci.'
group by i.id, i.name
having count(case when c.title = 'Intro. to Computer Science' then 1 end) = 0

Upvotes: 0

jawad-khan
jawad-khan

Reputation: 313

this should work for you

select i.name 
from instructor as i, teaches as t
where i.dept_name = 'Comp. Sci.' and i.ID=t.ID
and not exists (
  select 1 
  from course 
  where t.ID = course.course_id and title = 'Intro. to Computer Science'
)

Upvotes: 1

Related Questions