SQL Query Eliminate Multiple Table Calls of Same Table-CodePudding

I have the following query that I need to improve performance on. The biggest issue is that it's calling the same table multiple times, and I'm trying to see if there is a better way to execute it.

SELECT tc.chargesID
      ,p1.payerID as paymentPlan_Payer_1
      ,p2.payerID as paymentPlan_Payer_2
      ,p3.payerID as paymentPlan_Payer_3
      ,p4.payerID as paymentPlan_Payer_4
FROM TableCharges tc
LEFT JOIN Sales_Enc se ON c.chargesID  = se.salesencounterID

LEFT JOIN Sales_Enc_Payer sep1 ON sep.salesencounterID = se.salesencounterID and sep.salesCode = 1
LEFT JOIN Sales_Enc_Payer sep2 ON sep.salesencounterID = se.salesencounterID and sep.salesCode = 2
LEFT JOIN Sales_Enc_Payer sep3 ON sep.salesencounterID = se.salesencounterID and sep.salesCode = 3
LEFT JOIN Sales_Enc_Payer sep4 ON sep.salesencounterID = se.salesencounterID and sep.salesCode = 4

LEFT JOIN payer p1 ON sep1.payerID = p1.payerID
LEFT JOIN payer p2 ON sep2.payerID = p2.payerID
LEFT JOIN payer p3 ON sep3.payerID = p3.payerID
LEFT JOIN payer p4 ON sep4.payerID = p4.payerID

Let me know what you guys think.

CodePudding user response：

In line with what @Charlieface said try just joining to Sales_Enc_Payer once and also moving the salesCode = 1 join filters to the joins with the corresponding payer table instances like this...

SELECT
          tc.chargesID
        , p1.payerID AS paymentPlan_Payer_1
        , p2.payerID AS paymentPlan_Payer_2
        , p3.payerID AS paymentPlan_Payer_3
        , p4.payerID AS paymentPlan_Payer_4
FROM      TableCharges    tc
LEFT JOIN Sales_Enc       se ON tc.chargesID          = se.salesencounterID
LEFT JOIN Sales_Enc_Payer sep ON sep.salesencounterID = se.salesencounterID
LEFT JOIN payer           p1 ON sep.payerID           = p1.payerID
                            AND sep.salesCode         = 1
LEFT JOIN payer           p2 ON sep.payerID           = p2.payerID
                            AND sep.salesCode         = 2
LEFT JOIN payer           p3 ON sep.payerID           = p3.payerID
                            AND sep.salesCode         = 3
LEFT JOIN payer           p4 ON sep.payerID           = p4.payerID
                            AND sep.salesCode         = 4;

CodePudding user response：

I'm going to close this question, just going to have to work around this. Thanks everyone for the help!