Hey everyone! I’m working on a school project and I’m stuck. I’ve got a database with tables for students, courses, and universities. What I want to do is find all the students who are taking the same classes as a specific student. Here’s what I’ve tried so far:
SELECT DISTINCT s.name AS StudentName, s.id AS StudentID,
c.id AS CourseID, c.name AS CourseName,
u.id AS UniversityID
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
JOIN universities u ON u.id = e.university_id
WHERE s.id != 5 AND e.course_id IN (3, 4, 7)
GROUP BY s.name
ORDER BY e.enrollment_date
This kind of works, but it feels clunky. I’m especially worried about how to handle the IN clause when I’m actually using this in my app. Any ideas on how to make this more efficient or elegant? Thanks in advance for any help!
I’ve encountered a similar challenge in my database work. Your approach is solid, but we can optimize it further. Consider using a self-join on the enrollments table instead of a subquery. This method often performs better, especially with larger datasets:
SELECT DISTINCT s2.name, s2.id, c.id AS course_id, c.name AS course_name
FROM enrollments e1
JOIN enrollments e2 ON e1.course_id = e2.course_id
JOIN students s2 ON e2.student_id = s2.id
JOIN courses c ON e1.course_id = c.id
WHERE e1.student_id = 5 AND e2.student_id != 5
This query directly compares enrollments, finding matches for the specified student (ID 5) without needing to list courses separately. It’s more scalable and should execute faster. Remember to index your foreign keys for optimal performance. Let me know if you need any clarification on this approach.
yo ryan, have u tried using EXISTS? it’s pretty neat for this kinda stuff. something like:
SELECT s.name, s.id, c.id, c.name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE EXISTS (
SELECT 1 FROM enrollments e2
WHERE e2.student_id = 5 AND e2.course_id = e.course_id
) AND s.id != 5
this way u don’t need subqueries or self-joins. might be faster too!
Hey Ryan_Courageous! That’s an interesting project you’ve got there. 
I can see why you’re feeling a bit stuck - working with multiple tables can get tricky fast. Your query is on the right track, but I think we can tweak it to make it more flexible and efficient.
Have you considered using a subquery to find the courses for your specific student? Something like this might work:
SELECT DISTINCT s.name, s.id, c.id, c.*name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE e.course_id IN (
SELECT course_id
FROM enrollments
WHERE student_id = 5
) AND s.id != 5
This way, you don’t need to hardcode the course IDs, and it’ll automatically update if the student’s courses change.
What do you think? Does this approach make sense for your project? I’m curious to hear more about what you’re building - it sounds like it could be a really cool system for tracking student enrollment patterns!