These are the relevant tables for my question
modules
+--------+
| code |
+--------+
| CS1010 |
| CS2030 |
| CS2040 |
| IS3103 |
| IS2101 |
| CS3102 |
+--------+
prereqs
+--------+--------+
| code | need |
+--------+--------+
| CS2030 | CS1010 |
| CS2040 | CS1010 |
| IS3103 | IS2101 |
| CS3102 | CS2030 |
| CS3102 | CS2040 |
+--------+--------+
enrolls
+-----------+--------+
| matric | code |
+-----------+--------+
| A0000001A | CS1010 |
| A0000001A | CS2030 |
+-----------+--------+
In order to find all the modules (prereqs.code) that a student (enrolls.matric) is able to take, I have used the following query
SELECT P.code
FROM enrolls E, prereqs P
WHERE E.matric = 'A0000001A'
AND P.need IN (SELECT code FROM enrolls)
UNION
SELECT M.code
FROM modules M, prereqs P
WHERE M.code NOT IN (SELECT code FROM prereqs)
EXCEPT
SELECT code
FROM enrolls
WHERE matric = 'A0000001A';
The second subquery (after UNION) handles all modules that have no prerequisites and the third subquery (after EXCEPT) removes all modules that have already been taken by the student (A0000001A).
When running this query, CS3102 shows up as a module that is available for the student to take, this should not be the case as he has only taken CS2030 and not CS2040. In order to be able to take CS3102, the student needs to have taken both CS2030 and CS2040. I am not sure how to enforce this constraint with SQL. I am required to use PostgreSQL.
Currently, my query seems to show a module as being available to be taken as long as one of it's prequisites have been met. I am unsure how to modify my query to ensure that a module is only shown if all its prerequisites are met.
Current output:
+--------+
| code |
+--------+
| CS2040 |
| CS3102 |
| IS2101 |
+--------+
Desired output:
+--------+
| code |
+--------+
| CS2040 |
| IS2101 |
+--------+
Here is one approach using not exists and left joins:
select m.code
from modules m
left join prereqs pr on pr.code = m.code
left join enrolls e on e.code = pr.need and e.matric = 'A0000001A'
where not exists (select 1 from enrolls e where e.code = m.code and e.matric = 'A0000001A')
group by m.code
having count(pr.code) = count(e.code)
The not exists condition filters out modules that the given matric has already taken. On the other hands, the left joins bring the prequesites and the corresponding enrollments, and the having clause ensures that they are all fulfilled.