• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

Advanced SQL Question, linking 3 tables and getting a result?

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.
0
shadow_shooter
Asked:
shadow_shooter
  • 14
  • 11
1 Solution
 
racekCommented:
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;
0
 
brad2575Commented:
to get data from all 3 tables togehter this will work (but you will have to select the fields and add the where clause:

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

Open in new window

0
 
racekCommented:
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 Disaster Recovery in Microsoft Azure

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.

 
racekCommented:
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;
0
 
shadow_shooterAuthor Commented:
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?)
0
 
racekCommented:
:-)))))))) you are tooooo fast - have you seen my next message ???
0
 
shadow_shooterAuthor Commented:
racek,

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
0
 
racekCommented:
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;
0
 
racekCommented:


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;
0
 
racekCommented:
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;
0
 
shadow_shooterAuthor Commented:
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?
0
 
racekCommented:
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;
0
 
shadow_shooterAuthor Commented:
I think you got me wrong :(

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.
0
 
racekCommented:
do I need  A, 122 and 1 in this record?

1, A, "Mathematics", 122, 1

or is it enough with student id and Math.. ?
0
 
racekCommented:
if you execute the query below, what is mathstd for Computer Science ???

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 ;
0
 
shadow_shooterAuthor Commented:
Here is what it yields:



result.jpg
0
 
shadow_shooterAuthor Commented:
But there is a computer science student who is taking a Math course. So why is it 0?

=(
0
 
shadow_shooterAuthor Commented:
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.

So Computer Science shouldn't be listed.



0
 
racekCommented:


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;
0
 
racekCommented:
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;
0
 
shadow_shooterAuthor Commented:

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'

No luck...

0
 
shadow_shooterAuthor Commented:
THIS ONE WORKED:

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.
0
 
racekCommented:
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
0
 
shadow_shooterAuthor Commented:
It makes sense. racek, you're the best so far. thanks a million. You saved me! I wish I could award you more points than 500.

Thanks thanks thanks.
0
 
racekCommented:
:-))))))))
0
 
shadow_shooterAuthor Commented:
You are the best. I wish I could give more points!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 14
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now