Advanced SQL Question, linking 3 tables and getting a result?
Posted on 2009-04-17
I'm working on a project now but experiencing a problem with writing a query. My database scheme is as follows:
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.