Devesh Agrawal Devesh Agrawal - 8 days ago 6
MySQL Question

Inner join and left join not working as expected in mysql

I have 3 tables

table_supplier_bills - bill_id, supplier_id, date
table_supplier_bill_details - bill_id, product_id, quantity, rate
table_supplier_bill_payment_details - id, bill_id, payment_date, amount


I want to get all the bills with their bill_amount and paid_amount.

This is my query.

select
SB.bill_id,
SB.date, SB.supplier_id,
SUM(SBD.quantity * SBD.rate) as bill_amount,
COALESCE(SUM(SBPD.payment_amount), 0.00) as paid_amount
from table_supplier_bills SB
INNER JOIN
table_supplier_bill_details SBD
ON SB.bill_id = SBD.bill_id
LEFT JOIN table_supplier_bill_payment_details SBPD
ON SBD.bill_id = SBPD.bill_id
group by SBD.bill_id;


But this query doesn't give correct paid_amount if there are multiple rows in table_supplier_bill_details for a bill. in case of multiple rows query gives the paid_amount multiplied by as many rows are in table_supplier_bill_details for that table.

Can anyone help me what is wrong here?

Answer

Use a correlated query instead :

SELECT SB.bill_id, 
       SB.date,
       SB.supplier_id, 
       SUM(SBD.quantity * SBD.rate) as bill_amount, 
       COALESCE((SELECT SUM(SBPD.payment_amount)
                 FROM table_supplier_bill_payment_details SBPD 
                 WHERE SBD.bill_id = SBPD.bill_id ),0.00) as paid_amount 
FROM table_supplier_bills SB 
INNER JOIN table_supplier_bill_details SBD 
 ON SB.bill_id = SBD.bill_id
GROUP BY SBD.bill_id;
Comments