CODEWITHSUNDEEP
mysql
Mel_G
Mel_G

Reputation: 31

In MySQL - Looking for the names of students who are taking two courses

Here is my table:

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)

I have tried a couple of codes to come up with the solution to the question of "Print the names of students who are taking both a Computer Sciences course and a Mathematics course." However, I am not getting any results populated.

My first try "no results populated/no errors":

SELECT student.sid, student.sname
FROM student
INNER JOIN major ON major.sid = student.sid
AND major.dname LIKE '%Computer Sciences%' AND '%Mathematics%'
GROUP BY student.sname, major.dname

second try "no results populated/no errors":

SELECT student.sid, student.sname, course.dname
FROM student, course
INNER JOIN major ON major.dname = course.dname 
WHERE course.dname = '%Computer Sciences%'
AND course.dname = '%Mathematics%'

third try with error message: "Unknown column 'student.dname' in 'where clause'":

    SELECT student.sid, student.sname
    FROM student
    WHERE EXISTS (SELECT * FROM major WHERE major.dname=student.dname LIKE '%Computer Sciences%'  AND      '%Mathematics%')

Can someone please point me in the right direction...

Upvotes: 0

Views: 5461

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I would do this using aggregation and a having clause. I find this is much more generalizable than an approach using joins:

SELECT s.sid, s.sname
FROM student s INNER JOIN
     major m
     ON m.sid = s.sid
GROUP BY s.sname, s.sname
HAVING SUM(m.dname LIKE '%Computer Sciences%') > 0 AND
       SUM(m.dname LIKE '%Mathematics%') > 0;

Each condition in the having clause counts the number of students taking the particular courses. The > 0 says that there is at least one. If you want to add a third or fourth set of courses, that is very easy by adding more conditions in the HAVING clause.

Upvotes: 0

Edward
Edward

Reputation: 3276

You can use two separate joins, the first will test for the CS major and the second will test for the Maths:

SELECT student.sid, student.sname
FROM student
INNER JOIN enroll AS enroll1 
     ON enroll1.sid = student.sid AND enroll1.dname = '%Computer Sciences%'
INNER JOIN enroll AS enroll2
     ON enroll2.sid = student.sid AND enroll2.dname = '%Mathematics%'
GROUP BY student.sname, major.dname

Upvotes: 0

RobP
RobP

Reputation: 9542

I use more joins and inferred the relationships from what you provided. This returns student names and IDs where the student is in one class from each dept. GROUP BY is necessary only to eliminate duplicates in case student takes more than one course in one of these departments.

SELECT student.sid, student.sname
FROM student
JOIN enroll e1 ON e1.sid=student.sid
JOIN course c1 ON c1.cno=e1.cno
JOIN enroll e2 ON e2.sid=student.sid
JOIN course c2 on c2.cno = e2.cno
WHERE c1.dname LIKE '%Mathematics%'
AND c2.dname LIKE '%Computer Science%'
GROUP BY student.sid

Upvotes: 0

user3175748
user3175748

Reputation:

There are a few ways to do this, although none are particularly elegant. Here's one method:

SELECT student.sid, student.sname
FROM student
INNER JOIN major ON major.sid = student.sid
WHERE major.dname = 'Computer Sciences'
AND student.sid IN (
    SELECT student.sid
    FROM student
    INNER JOIN major ON major.sid = student.sid
    WHERE major.dname = 'Mathematics'
)

This gets a list of all the students in Mathematics, and then compares that list to see which of those students are in Computer Science. You end up with a list of students who are in both.

Upvotes: 1

Related Questions