How do I keep all values FROM Table 1 while joining multiple tables with conditions to Table 2, whic-CodePudding

I've googled and clicked through "Similar Questions" here, but I just can't find my answer. This is the closest I found, which was a post from here 9 years ago:

Btw, in my real work I have Table 4 joined to 3 or 2 and so on. Nothing joins back to Table 1 or 2 after Table 2's LEFT JOIN to Table 1.

CodePudding user response：

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.

CodePudding user response：

This is a comment that doesn't fit in the comments sections. What's wrong with this output of two left joins?

 T1_Cust 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                E 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                EMDv1         Pass       1                EMD          
 3       Bobby     Black     null         null            null             null          null       null             null

CodePudding user response：

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

CodePudding user response：

Add

where T3_ClassType is not null or T2_Customer_id is null

to your 2nd attempt

CodePudding user response：

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