Get sum across multiple tables

Question

Table example:

branch
----------------------------------
branchID | branchName | brMgrName|
----------------------------------
|      1  | a         |Tom       |
|      2  | b         |Jeff      |
|      3  | c         |Lin       |
----------------------------------

order
----------------------
orderID   | branchID |
----------------------
|   1     |     2   | 
|   2     |     2   | 
|   3     |     3   | 
----------------------


payment
--------------------------------
paymentID | orderID | p-amount |
--------------------------------
|   1     |     1   |   50.00  |
|   2     |     2   |  126.00  |
|   3     |     3   |   50.00  |
--------------------------------

The output that I want are

-------------------------------------------
| branchName | brMgrName | Total Amount   |
-------------------------------------------
|      a     |  Tom      |     0.00       |
|      b     |  Jeff     |   252.00       |
|      c     |  Lin      |    50.00       |
-------------------------------------------

I am trying to determine how to sum up the amount from each branch across 3 different tables.

In the output:
- branchName 'a' don't have any value from the order table,
- branchName 'b' was sum up by paymentID 1 & 2, and the same with
- branchName 'c'

I have successfully return both the branchName and Total Amount by joining branch and payment tables:

SELECT `order`.branchID, sum(`p-amount`)
FROM `order`
JOIN payment
ON `order`.orderID = payment.orderID
GROUP BY branchID

But when i attempt the following code, it return the wrong value and only 1 row have selected.

SELECT branchName, brMgrName, sum(`p-amount`)
FROM branch, payment
JOIN (SELECT `order`.branchID, sum(`p-amount`)
      FROM `order`
      JOIN payment
      ON `order`.orderID = payment.orderID
      GROUP BY branchID) b
ON b.branchID = branchID;

Any help is very much appreciated.

Answer 1

Try this:

SELECT b.branchName, brMgrName, sum(p.`p-amount`)
  FROM branch b
        LEFT JOIN `order` o
                ON b.branchID = o.branchID 
        LEFT JOIN payment p
                ON o.orderID = p.orderID 
GROUP BY b.branchName, brMgrName
ORDER BY 3 -- third column on select list