Need help with this select statement.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with this select statement.

Author

Topic

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-20 : 06:01:17

Here is the requirement...

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

Here are the tables...

Student(sid,sname,sex,age,year,qpa)
Dept(dname,numphds)
Prof (pname,dname)
Course (cno,cname,dname)
Major(dname,sid)
Section(dname,cno,sectno,pname)
Enroll(sid,grade,dname,cno,sectno)

Here is what I tried...

select distinct c.dname,
(select d.numphds from dept d where d.dname = c.dname) as "PHD STUDENTS"
From course c, major m
where c.dname = m.dname and c.dname not in ('College Geometry 1', 'College Geometry 2')

Here is my problem...

This just brings back all 6 of the departments and the number of phd students in those departments. I need it to only show the dname (with its respective numphds) where none of the majors in that dept are taking neither College Geometry 1 or College Geometry 2. Which means only Industrial Engineering should be in the results, instead it is showing all of them... I tried using the like predicate and other ways to limit but I either ended up continuing getting Mathematics by itself (wrong result) or all of them or all of them but Mathematics.

"dname" "numphds"
"Chemical Engineering" 32
"Civil Engineering" 88
"Computer Sciences" 47
"Industrial Engineering" 41
"Mathematics" 129
"Sanitary Engineering" 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-20 : 14:56:01

Sounds like this


SELECT m.dname
FROM Major m
LEFT JOIN(
SELECT sid
FROM  Major 
WHERE dname in ('College Geometry 1', 'College Geometry 2')
)m1
ON m1.sid = m.sid
GROUP BY m.dname
HAVING COUNT(m1.sid)=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-20 : 15:42:49

Yeah. That ran successfully but it is still showing all the departments still. I figured with how mine was setup it would only show Industrial Engineering. Seeing as how that is the only major where none of the students are taking either of the two college geometry courses.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-20 : 17:30:55

Sorry unless you show us how data is from your tables its quite difficult for us to come up with a suggestion. can you post some sample data and show required output from them in below format?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-20 : 20:02:02

The tables look exactly like this except maybe a few different values for the varchars and such.

http://www.cs.cornell.edu/Courses/cs432/2007fa/assignments/a2db.txt

Do you need some inserts as well? Here is a few of the many...

Insert into Student(sid,sname,sex,age,year,gpa)
values('25', 'Kissinger, Henry', 'm', '58', '5', '3.40')
Insert into Student(sid,sname,sex,age,year,gpa)
values('26', 'Ford, Gerald', 'm', '60', '5', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('27', 'Anderson, P.', 'f', '18', '1', '3.20')
Insert into Student(sid,sname,sex,age,year,gpa)
values('28', 'Austin, G.', 'm', '25', '5', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('29', 'Hamilton, S.', 'm', '21', '3', '2.80')
Insert into Student(sid,sname,sex,age,year,gpa)
values('30', 'Baker, C.', 'f', '18', '1', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('31', 'Andrews, R.', 'm', '19', '2', '2.80')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-20 : 21:17:17

do you mean no student enrolled for geometry courses?

ie


SELECT m.dname
FROM Major m
LEFT JOIN(
SELECT sid
FROM  enroll e
INNER JOIN course c
ON c.cno = e.cno
WHERE c.cname in ('College Geometry 1', 'College Geometry 2')
)m1
ON m1.sid = m.sid
GROUP BY m.dname
HAVING COUNT(m1.sid)=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources