How do I keep all values FROM Table 1 while joining multiple tables with conditions to Table 2, which has a LEFT JOIN to Table 1?

Question

Requirements: Have all the customers listed from the first table, then pull any classes that match conditions from the second table and third table, with the third table joined only to the second.

What I've tried:

1. When I use JOIN on the third table I don't get all the Customers (e.g., "Bobby Black").
2. When I use LEFT JOIN on the third table I get all the Customers, but then all the classes too (e.g., Jane Doe's "PST" class)!

Simplified Example:

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 SELECT * FROM @T2_Classes SELECT * FROM @T3_ClassTypes

--FIRST ATTEMPT
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'

--SECOND ATTEMPT    
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'

Results of Attempts & Desired Results: (T2_ClassName is abbreviated)

FIRST ATTEMPT

 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          

SECOND ATTEMPT

 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         

DESIRED RESULTS

 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         

Answer 1

Join with an INNER join @T2_Classes to @T3_ClassTypes and then do a LEFT join of @T1_Customers to that resultset:

SELECT * 
FROM @T1_Customers t1
LEFT JOIN (
  SELECT *
  FROM @T2_Classes t2 INNER JOIN @T3_ClassTypes t3
  ON t3.T3_ClassType_id = t2.T2_ClassType_id
  WHERE t2.T2_Status <> 'Fail' AND t3.T3_ClassType <> 'PST'
) t 
ON t.T2_Customer_id = t1.T1_Customer_id;

See the demo.

Answer 2

You are converting it to an implicit inner join by introducing a where there that filters on T2.

SELECT * FROM @T1_Customers
  LEFT JOIN (select * from @T2_Classes
  inner JOIN @T3_ClassTypes 
    ON T3_ClassType_id = T2_ClassType_id
  where T2_Status != 'Fail' AND T3_ClassType != 'PST') tmp
    ON T2_Customer_id = T1_Customer_id ;

DBFiddle demo

Answer 3

This looks like a good case for using an outer apply:

select c.*, cl.*
from customers c
outer apply(
  select cl.Class_Id, cl.ClassName, cl.[Status], ct.ClassType
  from Classes cl
    join ClassTypes ct on ct.ClassType_Id = cl.ClassType_Id
  where cl.Customer_Id = c.Customer_Id 
    and cl.[Status] != 'Fail'
    and ct.ClassType != 'PST'
)cl;

Demo Fiddle

Answer 4

Add

where T3_ClassType is not null or T2_Customer_id is null

to your 2nd attempt

Answer 5

SELECT 
t1.T1_Customer_id,t1.T1_FName,t1.T1_LName,
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 @T2_Classes t2
    INNER JOIN @T3_ClassTypes t3 ON t3.T3_ClassType_id = t2.T2_ClassType_id AND t3.T3_ClassType != 'PST'
ON t2.T2_Customer_id = t1.T1_Customer_id AND  t2.T2_Status != 'Fail'

Note the above to nest the INNER inside of the LEFT. The sample includes all columns, but adjust the SELECT as necessary.