Search code examples
sqlsql-serverjoinleft-joinsql-server-2016

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?


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         

Solution

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