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:
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
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.