I need to get all courses that are not a prerequisite for a certain course. I can do that by saying:
SELECT DISTINCT Course_Name FROM Courses, Prereqs
WHERE Prereqs.Prereq = Courses.Course_ID AND Prereqs.Course <> 60;
But the problem is, there are some Courses that are not prereqs for anything, so they are not listed in the Prereqs table, so they don't show up from that query. So here's how I solved that:
SELECT DISTINCT Course_Name FROM Courses
LEFT JOIN Prereqs ON Courses.Course_ID = Prereqs.Prereq
WHERE Prereqs.Course <> 60 OR Prereqs.Course IS NULL;
That works, I'm now getting all the courses, not just ones that are prerequisites for something.
The BIG problem is that a course can be a prerequisite for multiple courses, so for example I have a course number 13 that is a prerequisite for course 60 and 25, and this query adds course 13 to the result. But I want ONLY courses that are NOT prerequisites for 60, regardless if they are prerequisites for other courses.
Any idea how to achieve this? I think a MINUS query would work, but as far as I know access doesn't support minus.