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.

SELECT dname, count(*) students
FROM dept d
JOIN major m on d.dname = m.dname
JOIN (SELECT sid, SUM(IF(dname = 'Mathematics',1,0)) as mathstd
FROM ENROLL
GROUP BY 1 HAVING mathstd = 0) x
on x.sid = m.sid
GROUP BY 1;

sorry .. now only depts without 1 or more math studs

SELECT x.dname, x.students
FROM
(SELECT dname,
SUM(IF(dname = 'Mathematics',1,0)) as mathstd,
COUNT(distinct sid) students
FROM enroll ee
LEFT JOIN major mm on ee.sid= mm.sid
GROUP BY 1 HAVING mathstd = 0) x;

0

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

as you can see is table dept not used in the query, because you are not asking about printing numphds . if you wan it then:

SELECT x.dname, x.numphds, x.students
FROM
(SELECT dd.dname, dd.numphds,
SUM(IF(dd.dname = 'Mathematics',1,0)) as mathstd,
COUNT(distinct ee.sid) students
FROM enroll ee
JOIN major mm on ee.sid= mm.sid
JOIN dept dd on dd.dname = mm.dname
GROUP BY 1 HAVING mathstd = 0) x;

brad,
Unfortunately this doesn't yield the departments that do not have any students taking from Mathematics department.

Even Mathematics department itself is listed, which has many students in it who take Mathematics courses.

racek,
I'm getting "#1052 - Column 'dname' in field list is ambiguous" error for the line (SELECT dname, SUM(..."

By the way, you didn't use numphds column. Don't we need it to find num of phds? Because I'm not finding num of students but num of phds that is stored in numphds column of dept table (So, I think we don't need to use COUNT?)

Concerning your last query, it yields department name "Computer Science", though there is a student from this department who takes a course from Mathematics department.

For people, who want to try it on their PCs, I'm attaching the DB.

by the way - you have a redundant dname in enroll - you don't need major in this query ???

SELECT d.dname, d.numphds
FROM
(SELECT dname,
SUM(IF(dname = 'Mathematics',1,0)) as mathstd
FROM enroll GROUP BY 1 HAVING mathstd = 0) x
JOIN dept d on d.dname = x.dname;

SELECT d.dname, d.numphds
FROM
(SELECT sid,
SUM(IF(dname = 'Mathematics',1,0)) as mathstd
FROM enroll GROUP BY 1 HAVING mathstd = 0) x
JOIN dept d on d.dname = x.dname
JOIN enroll e on x.sid = e.sid and e.dname = x.dname
GROUP BY 1;

ooops 'Mathematics',1,0 should be 'Mathematics',0,1

SELECT x.dname, x.numphds
FROM
(SELECT dd.dname, dd.numphds,
SUM(IF(dd.dname = 'Mathematics',0,1)) as mathstd
FROM enroll ee
JOIN major mm on ee.sid= mm.sid
JOIN dept dd on dd.dname = mm.dname
GROUP BY 1 HAVING mathstd = 0) x;

We need it because dname in major and dname in enroll are defferent. dname in enroll table represent the department that the course is given by and dname in majors represents the departments of the students.

If I'm wrong let me know, but shouldn't I first find those students who don't take any courses from Mathematics department (using dname in enroll) and then their own departments using majors table?

it is to late in stockhol ... and lot of errors :-(

SELECT dd.dname, dd.numphds,
SUM(IF(ee.dname = 'Mathematics',1,0)) as mathstd
FROM enroll ee
JOIN dept dd on dd.dname = ee.dname
GROUP BY 1
HAVING mathstd = 0;

You found the departments which has NO Mathematics students. What I'm trying to find is

"For those departments that have no majors taking a Mathematics course, print the department name and the number of PhD students in the department."

For example, if you examine the database I attached, Poetry department doesn't have any students who take a course from the Mathematics department. So it should be listed.

But Computer Science department has a student who takes a course from Mathematics department, so it shouldn't be listed.

1 (sid) A (grade) Mathematics (dname) 332(cno) 2(sectno)

See? Mathematics is not the student's department. It is where the course with number 332 is found in. Student id is 1 and he is taking a Mathematics course.

If you check majors table and look up 1 in the table, you will see he is a Computer Science student.

SELECT d.dname, d.numphds, SUM(if(e.sid IS NOT NULL,1,0) ) cnt_std
FROM dept d JOIN enroll
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 cnt_std = 0;

SELECT d.dname, d.numphds, SUM(if(e.sid IS NOT NULL,1,0) ) cnt_std
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;

SELECT d.dname, d.numphds, SUM(if(e.sid IS NOT NULL,1,0) ) cnt_std
FROM dept d JOIN enroll ee
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

#1054 - Unknown column 'ee.cnt_std' in 'having clause'

SELECT d.dname, d.numphds, SUM( if( e.sid IS NOT NULL , 1, 0 ) ) cnt_std
FROM dept d
JOIN enroll ee ON ee.dname = d.dname
LEFT JOIN (
SELECT sid
FROM enroll
WHERE dname = 'Mathematics')e ON e.sid = ee.sid
GROUP BY 1
HAVING cnt_std =0

Oh blimey! Finally...

Thanks a tons... Could you please explain what it actually does? I mean, especially " if( e.sid IS NOT NULL , 1, 0 " and join methods used.

You can write later if you have to sleep :) You are so tired and I don't want to make you any more but will need an explanation tomorrow, if possible.

select math students:
(SELECT sid FROM enroll WHERE dname = 'Mathematics') e

join with all students ... LEFT JOIN ON e.sid = ee.sid
(LEFT JOIN givs you all students (sids) from ee!!! and only sid with math from e, otherwise NULL

if no match (NULL) add 1 to counter, else add 0
SUM( if( e.sid IS NOT NULL , 1, 0 ) ) cnt_std

remove records with students having mathematics
HAVING cnt_std =0

FROM dept d

JOIN major m on d.dname = m.dname

JOIN (SELECT sid, SUM(IF(dname = 'Mathematics',1,0)) as mathstd

FROM ENROLL

GROUP BY 1 HAVING mathstd = 0) x

on x.sid = m.sid

GROUP BY 1;