Find eligible courses based on completed prerequisites

Courses
1

I’m stuck trying to write a query that shows which courses a student can take based on their finished subjects. My database has three tables: Subjects, Courses, and a junction table linking them.

Here’s what I’ve tried:

SELECT DISTINCT C.*
FROM Courses C
JOIN CourseSubjects cs ON C.Id = cs.CourseId
WHERE cs.SubjectId IN (SELECT Id FROM Subjects WHERE Name IN ('Math', 'English', 'Art'))

But this isn’t quite right. It gives me courses that need any of those subjects, not just the ones where all prerequisites are met.

What I really want is to find courses where every required subject is in the student’s completed list. For example, if they’ve done Math, English, and Art, they should only see courses that need those subjects or fewer.

How can I change my query to do this? I’m new to SQL, so any tips would be great. Thanks!

2

Hey Ethan85! That’s a tricky problem you’ve got there. I’ve actually been working on something similar recently.

Have you considered using a GROUP BY with HAVING clause? It might help you match all prerequisites. Something like this could work:

SELECT C.*
FROM Courses C
JOIN CourseSubjects cs ON C.Id = cs.CourseId
WHERE cs.SubjectId IN (SELECT Id FROM Subjects WHERE Name IN ('Math', 'English', 'Art'))
GROUP BY C.Id
HAVING COUNT(DISTINCT cs.SubjectId) = (
    SELECT COUNT(*)
    FROM CourseSubjects
    WHERE CourseId = C.Id
)

This way, you’re only selecting courses where the count of matched prerequisites equals the total count of prerequisites for that course. Pretty neat, right?

What do you think about this approach? Have you tried anything like it before? I’m curious to hear if it works for your specific setup!

3

hey ethan, try filtering out missing prereqs using NOT EXISTS. e.g.

SELECT C.*
FROM Courses C
WHERE NOT EXISTS (
  SELECT 1 FROM CourseSubjects cs
  WHERE cs.CourseId = C.Id AND cs.SubjectId NOT IN (yourIDs)
);

Replace yourIDs with your subject ids. hope that helps!

4

I’ve encountered a similar challenge in my work. Here’s an approach that might solve your problem:

SELECT C.*
FROM Courses C
WHERE NOT EXISTS (
    SELECT 1
    FROM CourseSubjects cs
    WHERE cs.CourseId = C.Id
    AND cs.SubjectId NOT IN (
        SELECT Id
        FROM Subjects
        WHERE Name IN ('Math', 'English', 'Art')
    )
)

This query uses a subquery with NOT EXISTS to exclude any courses that have prerequisites not met by the student. It effectively checks if there are any subjects required for the course that are not in the student’s completed list.

The advantage of this method is that it’s efficient and scales well with larger datasets. It also handles cases where a course might have no prerequisites.

Have you considered how you’ll integrate this with a user interface to make it more dynamic? That could be an interesting next step in your project.