MySQL query help: Finding students enrolled in both CS and Math courses

Courses
1

I’m stuck trying to get a list of students taking both Computer Science and Math classes. Here’s my database setup:

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’ve tried a few queries but they’re not working. My latest attempt was:

SELECT s.sid, s.sname
FROM Student s
JOIN Enroll e1 ON s.sid = e1.sid
JOIN Enroll e2 ON s.sid = e2.sid
WHERE e1.dname = 'Computer Sciences'
AND e2.dname = 'Mathematics'

This didn’t return any results. What am I doing wrong? How can I fix my query to get the names of students taking both CS and Math courses? Thanks for any help!

2

Hey there, ExploringStars! :wave:

Your query is pretty close, but I can see why it’s not giving you the results you want. Let’s think about this a different way.

What if we used a single JOIN with the Enroll table and then grouped the results? Something like this might work:

SELECT s.sid, s.sname
FROM Student s
JOIN Enroll e ON s.sid = e.sid
WHERE e.dname IN ('Computer Sciences', 'Mathematics')
GROUP BY s.sid, s.sname
HAVING COUNT(DISTINCT e.dname) = 2

This query joins Student with Enroll once, filters for CS and Math departments, groups by student, and then only shows students who are in both departments.

What do you think? Give it a try and let me know if it works for you!

I’m curious, what class is this for? Are you working on a database project or just exploring SQL on your own? It’s always fun to dig into these kinds of problems! :blush:

3

Your approach is on the right track, but there’s a small tweak that could solve your issue. Instead of joining the Enroll table twice, try using a subquery with GROUP BY. Here’s a modified version that should work:

SELECT s.sid, s.sname
FROM Student s
WHERE s.sid IN (
    SELECT e.sid
    FROM Enroll e
    WHERE e.dname IN ('Computer Sciences', 'Mathematics')
    GROUP BY e.sid
    HAVING COUNT(DISTINCT e.dname) = 2
)

This query first finds all students enrolled in both CS and Math courses, then retrieves their details from the Student table. It’s more efficient and avoids potential issues with duplicate rows.

Remember to double-check the exact department names in your database, as they might be slightly different (e.g., ‘Computer Science’ instead of ‘Computer Sciences’). Let us know if this works for you!

4

hey mate, ur query’s close but needs a tweak. try this:\n\nSELECT s.sid, s.sname\nFROM Student s\nWHERE EXISTS (\n SELECT 1 FROM Enroll e\n WHERE e.sid = s.sid AND e.dname = ‘Computer Sciences’\n) AND EXISTS (\n SELECT 1 FROM Enroll e\n WHERE e.sid = s.sid AND e.dname = ‘Mathematics’\n)\n\nthis checks if a student exists in both depts. let me kno if it works!