如何在Claris FileMaker受限SQL中查询多对多关系的未关联记录

问题描述 #

我有一个通过中间表Attendance实现的典型多对多关系，涉及People、Courses、Attendance三张表，样例数据如下：

People表 #

Courses表 #

Attendance表 #

我已经通过以下SQL查询到所有选课人员的列表：

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

现在需要获取相反的结果：每个课程对应的未选课人员列表，预期结果如下：

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

我尝试了以下查询，但结果不符合预期——因为子查询并非针对单个课程执行，而是返回所有选过课的人员ID，导致结果是所有课程与已选课人员的组合：

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
)

环境限制 #

我使用的Claris FileMaker仅支持SQL-92的有限子集：

• 仅允许使用INNER JOIN、LEFT OUTER JOIN、CROSS JOIN
• 子查询仅能置于WHERE子句中

请问能否用符合该限制的基础SQL实现预期结果？

解决方案 #

可以通过生成所有课程-人员的全组合，再排除已存在于Attendance表中的组合来实现，以下两种写法均符合FileMaker的SQL限制：

方法1：使用NOT EXISTS关联子查询 #

SELECT Courses.CourseID, Courses.Course, People.PersonID, People.Name
FROM Courses
CROSS JOIN People
WHERE NOT EXISTS (
    SELECT 1
    FROM Attendance
    WHERE Attendance.CourseID = Courses.CourseID
      AND Attendance.PersonID = People.PersonID
)

方法2：使用NOT IN多列匹配（若FileMaker支持） #

如果FileMaker支持多列NOT IN，也可以用更简洁的写法：

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

逻辑说明 #

1. CROSS JOIN会生成所有课程与所有人员的笛卡尔积，也就是所有可能的选课组合；
2. 通过WHERE子句中的子查询，筛选出不存在于Attendance表的组合——这些就是对应课程的未选课人员。

对比你之前的错误查询，这里的子查询会关联外层的CourseID和PersonID，确保是针对当前课程-人员组合做判断，而非全局筛选已选课人员。

内容的提问来源于stack exchange，提问作者y.arazim