user466061 user466061 - 1 month ago 4
MySQL Question

How to select mysql SUM from multiple tables and other columns from another table?

I have three tables: cash, cheque and bill. All three tables share two common columns: billId and customerId.

Bill Table

+--------+------------+---------+------------+
| billId | date | bAmount | customerId |
+--------+------------+---------+------------+
| #1 | 01-05-2016 | 120.00 | 2 |
+--------+------------+---------+------------+
| #2 | 10-05-2016 | 100.00 | 2 |
+--------+------------+---------+------------+
| #3 | 20-05-2016 | 80.00 | 2 |
+--------+------------+---------+------------+
| #4 | 20-05-2016 | 70.00 | 2 |
+--------+------------+---------+------------+
| #5 | 27-05-2016 | 50.00 | 2 |
+--------+------------+---------+------------+
| #6 | 28-05-2016 | 20.00 | 2 |
+--------+------------+---------+------------+


Cheque Table

+----------+--------+------------+----------+
| chequeId | billId | customerId | chAmount |
+----------+--------+------------+----------+
| 1 | #1 | 2 | 50.00 |
+----------+--------+------------+----------+
| 2 | #2 | 2 | 25.00 |
+----------+--------+------------+----------+
| 3 | #5 | 2 | 36.00 |
+----------+--------+------------+----------+
| 4 | #4 | 2 | 23.00 |
+----------+--------+------------+----------+


Cash Table

+--------+--------+------------+----------+
| cashId | billId | customerId | caAmount |
+--------+--------+------------+----------+
| 1 | #1 | 2 | 55.00 |
+--------+--------+------------+----------+
| 2 | #2 | 2 | 70.00 |
+--------+--------+------------+----------+
| 3 | #3 | 2 | 69.00 |
+--------+--------+------------+----------+
| 4 | #4 | 2 | 23.00 |
+--------+--------+------------+----------+


I have to generate a query to generate results like below:

+--------+------------+--------+---------+
| billId | date | amount | pending |
+--------+------------+--------+---------+
| #1 | 01-05-2016 | 120.00 | 15.00 |
+--------+------------+--------+---------+
| #2 | 10-05-2016 | 100.00 | 05.00 |
+--------+------------+--------+---------+
| #3 | 20-05-2016 | 80.00 | 11.00 |
+--------+------------+--------+---------+
| #4 | 20-05-2016 | 70.00 | 14.00 |
+--------+------------+--------+---------+
| #5 | 27-05-2016 | 50.00 | 04.00 |
+--------+------------+--------+---------+


I am sending a value for customerID to this page from another page, like
$customerId = $_REQUEST['customerId']
and from this I have to select BillId and Date from the Bill Table, amount (which is computed by the sum of
chAmount+caAmount
), and pending (which is computed by the difference of
bAmount-(chAmount+caAmount)
). Since billId #6 doesn't have any records in the cheque and cash tables it doesn't need to be yielded in the results. Please mention a proper MySql query and explain it.

Answer

Normally, you should try something but as I am in my "good days" I wrote the full SQL statement :

SELECT b.billId, b.bDate, b.bAmount, SUM(ch.chAmount) chAmountSum, SUM(ca.caAmount) caAmountSum, (b.bAmount - ( IFNULL(SUM(ch.chAmount), 0)  + IFNULL(SUM(ca.caAmount), 0))) pending
FROM bill b
LEFT JOIN  cheque ch on ch.billId = b.billId
LEFT JOIN  cash ca on ca.billID = b.billId
GROUP BY b.billID;
  • You have to select all columns that you want to display
  • For the last one (pending), make your operation (amount - (cheque + cash) and if it is a null value, replace it by "0" thanks to IFNULL SQL function
  • GROUP BY from your reference column : billID