[SOLVED] Sum of column is not working in join table PHP

Sum of column is not working in join table PHP

I am trying to calculate the req_qty and in_qty from table1 and table2. I am trying to fetch the joined 3 tables data SUM(req_qty), SUM(in_qty). Issue come if req.qty not save in table2 it will not works. I am left joining the table by this sku's ids

I made here a very simple table for calculating the sum of the column. generally, the sum of the column is multiplying the total rows.

table1

ID  |  sku_1 | req_qty | trans_id
----------------------------------
1   |  123   | 150     | 12345
2   |  142   | 200     | 256314
3   |  123   | 100     | 896523

table2

ID  | sku_2  | in_qty  | trans_key
-----------------------------------
1   | 142    | 50      | 002563

table3

ID |  sku_code  |  sku_name
--------------------------
1  | 142        |  ABC
2  | 123        |  XYZ

Expected Output

ID | sku | sku_name | reqQty | inQty 
------------------------------------
1  | 123 | XYZ      | 250    | 0        
2  | 142 | ABC      | 200    | 50

Solution

Edit to select data when table1 and table2 are empty

SELECT table1.id, table3.sku_code as sku, table3.sku_name, 
       sum(table1.req_qty) as reqQty, sum(table2.in_qty) as inQty
  FROM table3
  LEFT JOIN table1 on table3.sku_code = table1.sku_1
  LEFT JOIN table2 on table3.sku_code = table2.sku_2
  GROUP BY table1.id, table3.sku_code, table3.sku_name

Explanation

You can see an explanation on how left join works here https://www.w3schools.com/sql/sql_join_left.asp#:~:text=The%20LEFT%20JOIN%20keyword%20returns,if%20there%20is%20no%20match.

But to explain this query quickly, we will select all data from table3, left join will find all records from left table (here table3) and mathcing records from right tables (here table2 and table 1).