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;

Solved

Posted on 2009-04-17

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.

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.

26 Comments

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;

This will get ALL results from all 3 tables (and ones that are blank as well).

you can reference each field from each table by adding the "alias" to the field name.

So dp.numphds or mj.sid as examples.

```
Select *
From dept dp left outer join
major mj on dp.dbname = mj.dbname left outer join
enroll en on dp.dbname = en.dbname
```

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;

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;

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.

database.txt

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;

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;

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?

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.

I hope it makes sense.

I really appreciate your efforts.

1, A, "Mathematics", 122, 1

or is it enough with student id and Math.. ?

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 ;

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.

So Computer Science shouldn't be listed.

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;

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

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 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

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Need help creating a stored procedure | 8 | 50 | |

Could you check what is preventing this MySQL query to correctly running? | 2 | 26 | |

How to restrict access to pages but still have my content indexed by Google? | 4 | 23 | |

Filter by data and current_date | 4 | 15 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**18** Experts available now in Live!