Hello,

I'm working on a project now but experiencing a problem with writing a query. My database scheme is as follows:

dept(dname, numphds)

major(dname, sid)

enroll(sid, grade, dname, cno, sectno)

What I want to do is printing the department name and the number of PhD students for those departments that have no majors taking a Mathematics course. (Mathematics is a department name).

Enroll table has a dname which tells about the department name that the course is given by. For example, record: (1, A, "Mathematics", 122, 1) tells that the student no. 1 is taking a Mathematics course whose number is 122 and section number 1. So, if he is a Computer Science student, then Computer Science should be excluded from the results.

Major table has a dname and sid that holds information about what major each student is.

So I have to find departments that have students who do not take any courses from the Mathematics department.

Which MySQL query do I need to achieve this? I can find the students who don't take any courses from Mathematics (for example a Computer Science student), but the problem is that another student from the same department (Computer Science) MIGHT be taking a course from the Mathematics department. So NONE of students should take a course from Mathematics. I can't build this structure.

It's so important, so any help would be indeed appreciated.

Thank you.

FROM dept d JOIN enroll ee

ON on ee.dname = d.dname

LEFT JOIN (SELECT sid FROM enroll WHERE dname = 'Mathematics') e

on e.sid= ee.sid

GROUP BY 1

HAVING ee.cnt_std = 0;