Querying for students enrolled in two specific courses

Courses
1

I’m stuck on a database query. I need to find students who took both ‘Data Structures’ and ‘Software Engineering’ classes. My current query works for each course separately but returns nothing when I try to combine them. Here’s what I’ve tried:

SELECT s.name, s.email
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
WHERE c.name = 'Data Structures' AND c.name = 'Software Engineering'
GROUP BY s.id
HAVING COUNT(DISTINCT c.id) = 2

This gives me zero results. I think the problem is in the WHERE clause but I’m not sure how to fix it. Can anyone help me figure out how to list students who’ve taken both courses? I’ve been stuck on this for hours and would really appreciate some guidance!

2

hey mate, ur query’s almost there! just swap that AND in the WHERE clause for an OR:

WHERE c.name = ‘Data Structures’ OR c.name = ‘Software Engineering’

this’ll grab students from both courses, then the HAVING part’ll filter for those who took both. hope this helps ya out!

3

Your query structure is sound, but the WHERE clause is the culprit here. A single course can’t have two different names simultaneously. Here’s a revised version that should work:

SELECT s.name, s.email
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
WHERE c.name IN ('Data Structures', 'Software Engineering')
GROUP BY s.id, s.name, s.email
HAVING COUNT(DISTINCT c.name) = 2

This query uses IN to match either course name, then the HAVING clause ensures we only return students enrolled in both. The GROUP BY clause includes all non-aggregated columns to comply with SQL standards. Let me know if you encounter any issues with this approach.

4

Hey LeoNinja22! :man_detective:

Your query’s got the right idea, but there’s a tiny tweak that’ll make it work like a charm. Mind if I ask what database system you’re using? Some SQL flavors have neat tricks we could use.

Have you considered using IN instead of OR? It might look cleaner:

WHERE c.name IN ('Data Structures', 'Software Engineering')

Curious - how many students are you expecting to get? It’d be cool to know if the results match what you’re thinking. Also, any chance you need to factor in different semesters or years for these courses?

Let me know how it goes after you try these tweaks. Always fun to crack these query puzzles! :smile: