Select unrelated in a n:m relationship

I have a typical many-to-many relationship resolved by a join table, as shown here:

For this question the sample data is:

People:

Courses:

Attendance:

The following query:

SELECT Courses.CourseID, Courses.Course, People.PersonID, People.Name
FROM Courses
INNER JOIN Attendance ON Courses.CourseID = Attendance.CourseID
INNER JOIN People ON Attendance.PersonID = People.PersonID

gives me a flat list of attendances by course:

C001,Algebra,P001,Alice
C001,Algebra,P002,Bob
C001,Algebra,P003,Carlos
C002,Biology,P002,Bob
C002,Biology,P003,Carlos
C003,Chemistry,P003,Carlos

Now I am trying to get a similar, but opposite result – i.e. a list of people who did NOT attend each course, which should look like this:

Expected result:

C001,Algebra,P004,David
C002,Biology,P001,Alice
C002,Biology,P004,David
C003,Chemistry,P001,Alice
C003,Chemistry,P001,Bob
C003,Chemistry,P004,David

I was hoping I could use something like:

SELECT Courses.CourseID, Courses.Course, People.PersonID, People.Name
FROM Courses
CROSS JOIN People
WHERE People.PersonID IN (
SELECT Attendance.PersonID
FROM Courses
INNER JOIN Attendance ON Attendance.CourseID = Courses.CourseID
)

but unfortunately that returns:

C001,Algebra,P001,Alice
C001,Algebra,P002,Bob
C001,Algebra,P003,Carlos
C002,Biology,P001,Alice
C002,Biology,P002,Bob
C002,Biology,P003,Carlos
C003,Chemistry,P001,Alice
C003,Chemistry,P002,Bob
C003,Chemistry,P003,Carlos

which suggests that the subquery is performed once for all courses, not individually for each course.

IMPORTANT:
The application I am using (Claris FileMaker) implements a very limited subset of the SQL-92 standard (and isn’t very well documented to boot). I can only use the SELECT statement with a handful of clauses, and AFAICT only INNER JOIN, LEFT OUTER JOIN and CROSS JOIN are allowed. Also I haven’t been able to implement a subquery anywhere except inside a WHERE clause.

Is it at all possible to return the expected result using only plain, basic SQL?

You need to sign in to view this answers