I have 4 tables:
1-Supplier
| ID_A | Supplier name |
|---|---|
| 1 | apple |
| 2 | Millet |
| 3 | Nokia |
| 4 | OPPO |
2-Start Balance
| ID_B | Starting balance |
|---|---|
| 1 | 1000 |
| 2 | 1000 |
| 3 | 1000 |
| 4 | null |
3- Invoice
| ID_C | Invoice value |
|---|---|
| 1 | 200 |
| 1 | 500 |
| 2 | 800 |
| 3 | 250 |
| 3 | 400 |
| 4 | null |
4-Return
| ID_D | Return_value |
|---|---|
| 1 | 100 |
| 2 | 50 |
| 2 | 25 |
| 3 | null |
| 4 | null |
Arithmetic method for displaying data as a result:
Starting Balance Invoice - Return = Final Balance
I tried using UNION and JOINS in mysql:
SELECT null , Supplier_name , ID_A , SUM(Invoice_value) , null , null FROM Suppliers
inner JOIN Invoices
ON ID_A = ID_C
group by ID_A
UNION ALL
SELECT null , Supplier_name , ID_A , null , SUM(Return_value), null FROM Suppliers
left JOIN Returns
ON ID_A = ID_D
group by ID_A
UNION ALL
SELECT Start Balance , Supplier_name, ID_A , null , null ,( Start Balance + ifnull(SUM(Invoice_value),0) - ifnull(SUM(Return_value),0) ) FROM Suppliers
left JOIN Start Balance
ON ID_A = ID_B
left JOIN Invoices
ON ID_A = ID_C
left JOIN Returns
ON ID_A = ID_D
group by ID_A
I expect this result to be:
| Starting balance | Supplier name | ID_A | Invoice value | Return_value | End_Balance |
|---|---|---|---|---|---|
| 1000 | apple | 1 | 700 | 100 | 1600 |
| 1000 | Millet | 2 | 800 | 75 | 1725 |
| 1000 | Nokia | 3 | 650 | null | 1650 |
| null | OPPO | 4 | null | null | null |
But it doesn't work well, it shows the results in different rows and the calculation of the final balance is wrong What is the correct code to display this result?
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
You can sum the sums in subqueries and concatenate them together
SELECT `Start Balance`, Supplier_name , ID_A , `Invoice_value` , `Return_value` , `Start Balance` + IFNULL(Invoice_value,0) - ifnull(Return_value,0) total FROM Suppliers left JOIN Start_Balance ON ID_A = ID_B left JOIN ( SELECT `ID_C`, SUM(`Invoice_value`) `Invoice_value` FROM Invoices GROUP By ID_C) i ON ID_A = ID_C left JOIN ( SELECT `ID_D`, SUM(`Return_value`) `Return_value` FROM Returns GROUP BY `ID_D`) r ON ID_A = ID_Dviolin