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

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

                                              

1:
2:
3:
4:

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;

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

:-)))))))) you are tooooo fast - have you seen my next message ???

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.

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;

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.

do I need  A, 122 and 1 in this record?

1, A, "Mathematics", 122, 1

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

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 ;

Here is what it yields:

But there is a computer science student who is taking a Math course. So why is it 0?

=(

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.

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

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.

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

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.

:-))))))))