Search code examples
mysql

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

Solution

  • 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