I want to get students who have not taken any courses who are taught by Jay. From the tables below, all of the students at least have one of their courses taught by Jay. Therefore, the query needs to return empty.

Student

Lecturer

Course

Enroll

My query

SELECT s.*
FROM student AS s
JOIN enroll AS e ON s.student_id = e.student_id
WHERE e.course_id NOT IN ("c001")

What I get

What I expect

I expected it to returns empty records since Phil (s001) has taken a course taught by Jay. Because he has two courses, which have Gloria as the lecturer, it then returns Phil. I'm not sure how to check any records from a group of similar records (same student_id, different course_id) match a certain course_id

CodePudding user response：

Your query asks which students have classes besides 'c001' which is a different question. Instead think of sets, I want to see all students except the ones that took the course 'c001':

select name
from student
where student_id not in (
  select student_id from enroll where course_id = 'c001'
)

I created the minimal schema required to run that query at db-fiddle.com for you.

The next step is to replace the inner query with a condition on lecturer name (untested):

select student_id
from lecturer
join course using (lecturer_id)
join enroll using (course_id)
where lecturer.name = 'Jay'

CodePudding user response：

would group_by student_id, and choose those having a positive value for not enrolled in "c001"

select s.*, sum(case when course_id in ("c001") then 1 else 0 end) = 0 not_jay
from student s join enroll e on e.student_id = s.student_id
group by s.student_id
having not_jay > 0
;

detailed SQL Fiddle here

CodePudding user response：

using left join and subquery

select s.*
from student s
left join (
  select e.student_id, c.course_id, c.name cname, l.lecturer_id, l.name lname
  from enroll e
  join course c on e.course_id = c.course_id
  join lecturer l on c.lecturer_id = l.lecturer_id
  where l.name = 'Jay'
) ecl on s.student_id = ecl.student_id
where ecl.student_id is null