如何在多表关联时保留所有T1客户并筛选符合条件的T2、T3数据？

需求 #

保留T1_Customers表中的所有客户，同时提取T2_Classes表和T3_ClassTypes表中符合以下条件的数据：

• 课程状态不为Fail
• 课程类型不为PST
• T3_ClassTypes仅与T2_Classes关联

已尝试方案及问题 #

• 使用JOIN关联T3_ClassTypes：无法获取全部客户（例如客户Bobby Black会被排除）
• 使用LEFT JOIN关联T3_ClassTypes：能保留全部客户，但会带出不符合类型要求的课程（例如Jane Doe的PST课程）

简化示例代码 #

DECLARE @T1_Customers TABLE 
(T1_Customer_id INT, 
 T1_FName VARCHAR(50), 
 T1_LName VARCHAR(50))
INSERT INTO @T1_Customers VALUES 
(1,'John','Darwin'),
(2,'Jane','Doe'),
(3,'Bobby','Black')

DECLARE @T2_Classes TABLE 
(T2_Class_id INT, 
 T2_Customer_id INT, 
 T2_ClassType_id INT, 
 T2_ClassName VARCHAR(50), 
 T2_Status VARCHAR(50))
INSERT INTO @T2_Classes VALUES 
(1,1,1,'Emergency Medical Dispatch v1','Pass'),
(2,1,2,'Emergency Medical Dispatch Instructor','Pass'),
(3,2,3,'Public Safety Telecommunicator','Pass'),
(4,2,1,'Emergency Medical Dispatch v1','Pass'),
(5,2,1,'Emergency Medical Dispatch v2','Fail')

DECLARE @T3_ClassTypes TABLE 
(T3_ClassType_id INT, 
 T3_ClassType VARCHAR(50))
INSERT INTO @T3_ClassTypes VALUES 
(1,'EMD'),
(2,'EMD-I'),
(3,'PST')

-- 第一次尝试
SELECT * FROM @T1_Customers
  LEFT JOIN @T2_Classes 
    ON T2_Customer_id = T1_Customer_id 
      AND T2_Status != 'Fail'
  JOIN @T3_ClassTypes 
    ON T3_ClassType_id = T2_ClassType_id
      AND T3_ClassType != 'PST'

-- 第二次尝试    
SELECT * FROM @T1_Customers
  LEFT JOIN @T2_Classes 
    ON T2_Customer_id = T1_Customer_id 
      AND T2_Status != 'Fail'
  LEFT JOIN @T3_ClassTypes 
    ON T3_ClassType_id = T2_ClassType_id
      AND T3_ClassType != 'PST'

尝试结果与期望结果（T2_ClassName已缩写） #

第一次尝试结果 #

T1_Customer_id T1_FName  T1_LName  T2_Class_id  T2_Customer_id  T2_ClassType_id  T2_ClassName  T2_Status  T3_ClassType_id  T3_ClassType 
 -------------- --------- --------- ------------ --------------- ---------------- ------------- ---------- ---------------- ------------ 
 1              John      Darwin    1            1               1                EMD v1        Pass       1                EMD          
 1              John      Darwin    2            1               2                EMDI          Pass       2                EMD-I        
 2              Jane      Doe       4            2               1                EMD v1        Pass       1                EMD          

第二次尝试结果 #

T1_Customer_id T1_FName  T1_LName  T2_Class_id  T2_Customer_id  T2_ClassType_id  T2_ClassName  T2_Status  T3_ClassType_id  T3_ClassType 
 -------------- --------- --------- ------------ --------------- ---------------- ------------- ---------- ---------------- ------------ 
 1              John      Darwin    1            1               1                EMD v1...     Pass       1                EMD          
 1              John      Darwin    2            1               2                EMDI...       Pass       2                EMD-I        
 2              Jane      Doe       3            2               3                PST...        Pass       null             null          
 2              Jane      Doe       4            2               1                EMD v1...     Pass       1                EMD          
 3              Bobby     Black     null         null            null             null          null       null             null         

期望结果 #

T1_Customer_id T1_FName  T1_LName  T2_Class_id  T2_Customer_id  T2_ClassType_id  T2_ClassName  T2_Status  T3_ClassType_id  T3_ClassType 
 -------------- --------- --------- ------------ --------------- ---------------- ------------- ---------- ---------------- ------------ 
 1              John      Darwin    1            1               1                EMD v1        Pass       1                EMD          
 1              John      Darwin    2            1               2                EMDI          Pass       2                EMD-I        
 2              Jane      Doe       4            2               1                EMD v1        Pass       1                EMD          
 3              Bobby     Black     null         null            null             null          null       null             null         

解决方案 #

先对T2_Classes和T3_ClassTypes进行内连接并过滤不符合条件的数据，再将结果与T1_Customers做左连接，确保保留所有客户的同时只带出符合要求的课程：

SELECT 
    T1.*,
    T2.T2_Class_id,
    T2.T2_Customer_id,
    T2.T2_ClassType_id,
    T2.T2_ClassName,
    T2.T2_Status,
    T3.T3_ClassType_id,
    T3.T3_ClassType
FROM @T1_Customers T1
LEFT JOIN (
    SELECT T2.*, T3.*
    FROM @T2_Classes T2
    INNER JOIN @T3_ClassTypes T3 
        ON T2.T2_ClassType_id = T3.T3_ClassType_id
        AND T3.T3_ClassType != 'PST'
    WHERE T2.T2_Status != 'Fail'
) AS FilteredClasses ON FilteredClasses.T2_Customer_id = T1.T1_Customer_id

也可以调整连接顺序，将过滤条件整合到LEFT JOIN的关联逻辑中：

SELECT *
FROM @T1_Customers T1
LEFT JOIN @T2_Classes T2 
    ON T2.T2_Customer_id = T1.T1_Customer_id
    AND T2.T2_Status != 'Fail'
    AND EXISTS (
        SELECT 1 
        FROM @T3_ClassTypes T3 
        WHERE T3.T3_ClassType_id = T2.T2_ClassType_id
        AND T3.T3_ClassType != 'PST'
    )
LEFT JOIN @T3_ClassTypes T3 
    ON T3.T3_ClassType_id = T2.T2_ClassType_id

两种写法的核心思路都是先过滤出符合条件的课程记录，再与客户表做左连接，避免直接关联时出现的客户丢失或无效课程带出的问题。

内容的提问来源于stack exchange，提问作者DanielT