多个左连接和

Question

I'm trying to display in a table (with data tables plugin) informations with sum from 3 tables using Left Join in sql query. I succeeded to edit server-side query and display correct datas with first jointure between two tables (t1=...budget & t2=..budget_changes) using the following query :

$year=date('Y');

$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns)).", 

IFNULL(SUM(t2.change_amount),0) AS operation_changes,
(t1.operation_BP+IFNULL(SUM(t2.change_amount),0)) AS operation_total 
FROM budget AS t1 

LEFT JOIN wp_dri_budget_changes AS t2 ON t2.change_year_operation=t1.operation_year_number 

WHERE t1.operation_year=".$year." AND t1.operation_active=1 $sWhere

GROUP BY operation_year_number, change_year_operation $sOrder $sLimit";

But when I'm trying to connect 3 tables with Left joint query the sum results are wrong.

$year=date('Y');

$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns)).",

IFNULL(SUM(t2.change_amount),0) AS operation_changes,

(t1.operation_BP+IFNULL(SUM(t2.change_amount),0)) AS operation_total,

IFNULL(SUM(t3.expense_enga_amount),0) AS operation_consommation

FROM budget AS t1

LEFT JOIN wp_dri_budget_changes AS t2 ON t2.change_year_operation=t1.operation_year_number

LEFT JOIN wp_dri_budget_expenses AS t3 ON t3.expense_year_operation=t1.operation_year_number 

WHERE t1.operation_year=".$year." AND t1.operation_active=1 $sWhere GROUP BY operation_year_number, change_year_operation, expense_year_operation $sOrder $sLimit";

What's wrong with this query ? Many Thanks MT

Answer 1

The problem might be the fact due to a Cartesian result of your data and summations going on. Just to clarify, here is a simple query... I know I don't have it all, nor join columns perfect, this is just for clarification.

ALSO, I KNOW I have abbreviated the columns and aliases for simplified reading and understanding of the concept of what you are probably encountering.

Select
      t1.yr,
      sum( t2.Amt ) as AmtChange
   FROM 
      budget AS t1
         LEFT JOIN Budget_Changes AS t2
            on t1.yr = t2.Yr

At the end, no problem... for a given year, you will get the totals from the second table. There are many records in table 2. Ex: Data

Budget
Yr
2013
2014

Budget_Changes
Yr    Amt
2013  10
2013  20
2013  30
2014  40
2014  50

Your results would be
Yr    AmtChange
2013  60
2014  90

We probably agree on that at this point... Now, throw in another table that per year (or whatever), that too has multiple records per year...

Change_Orders
Yr     COAmt
2013   100
2013   120
2014   200
2014   220

And you add this in as a secondary left-join to your query, something like

Select
      t1.yr,
      sum( t2.Amt ) as AmtChange,
      sum( t3.COAmt ) as COAmtChange
   FROM 
      budget AS t1
         LEFT JOIN Budget_Changes AS t2
            on t1.yr = t2.Yr
         LEFT JOIN Change_Orders AS t3
            on t1.yr = t3.Yr


Your might expect the results to be
Yr    AmtChange  COChangeAmt
2013  60         220
2014  90         420

However, since it is a Cartesian result... multiple rows per each join is taking the results TIMES each entry that exists in the other table... something like

Yr    AmtChange  COChangeAmt
2013  120         440
2014  180         840

To fix this, each individual table you are getting subtotals from should be handled on its own, and grouped by its own year so the subset returns only one row per context of data. Something like

Select
      t1.yr,
      t2.AmtChange,
      t3.COAmtChange
   FROM 
      budget AS t1
         LEFT JOIN ( select BC.Yr, sum( BC.Amt ) as AmtChange
                        from Budget_Changes BC
                        group by BC.Yr ) t2
            on t1.yr = t2.Yr
         LEFT JOIN ( select CO.Yr, sum( CO.COAmt ) as COAmtChange
                        from Change_Orders CO
                        group by CO.Yr ) AS t3
            on t1.yr = t3.Yr

So, the sub-queries will each return only 1 record for the respective year being aggregated and thus prevent the duplicate in sum() amounts.