Total amounts from two different tables.
Posted on 2006-06-29
I have three tables as shown below. Table A is a list of shipments. Table B is a list of Charges for each shipment. Table C is a list of charges on each invoice.
Table A (ShipID is a auto increment)
1 Mary Boyd
2 Tom Delay
3 George Bush
Table B (ChargeID is a auto increment, ShipID is a foreign key)
ChargeID ShipID ChargeType ChargeAmount
1 1 BaseCharge 6.00
2 1 Fuel 6.00
3 2 BaseCharge 12.50
4 2 Fuel 3.00
5 3 CustomsFees 55.00
Table C (ChargeID is a foreign key)
ChargeID Amount Invoice#
1 6.00 55245
2 5.00 55245
3 9.00 55246
3 3.50 55247
4 3.00 55246
5 49.00 55248
There can be multiple records in Table C for each record in Table B and there can be multiple records in Table B for each in Table A.
I need a sql statement that can display the following information.
ShipID TotalChargedAmount TotalInvoiceAmount Balance
1 12.00 11.00 1.00
2 15.50 15.50 0.00
3 55.00 49.00 6.00
I have one statement but it take a really long time to calculate. I need a fast sql statement. The following statement takes about 25 seconds. I need a very quick statement (under 1 sec) I have about 10000 records in each table.
select TableA.ShipID, sum(TotalBilledSQL.TotalBilled) AS `AmountPaid`,sum(TotalChargeSQL.TotalCharges) AS `OrigAmount`
,(sum(TotalChargeSQL.TotalCharges)- sum(TotalBilledSQL.TotalBilled)) AS `Balance` from ((TableA left join (SELECT TableB.ShipID, sum(TableB.ChargeAmount) AS TotalCharges FROM TableB GROUP BY TableB.ShipID) AS TotalChargeSQL on((TableA.ShipID = TotalChargeSQL.ShipID))) left join (SELECT TableB.ShipID, sum(TableC.Amount) as TotalBilled FROM TableC RIGHT JOIN TableB ON TableB.ChargeID = TableC.ChargeID GROUP BY TableB.shipid) as TotalBilledSQL on((TableA.shipid = TotalBilledSQL.ShipID))) group by TableA.ShipID