mysql - Get SUM of two fields in 2 different related tables -

i'd sum of amount column in 2 related tables.

invoices table:

----------------------------------------- | id  | student_id  | created | updated | ----------------------------------------- |  5  |     25      | date    | date    | ----------------------------------------- 

invoice items table:

------------------------------ | id  | invoice_id  | amount | ------------------------------ |  1  |     5       | 250    | ------------------------------ |  2  |     5       | 100    | ------------------------------ |  3  |     5       | 40     | ------------------------------ 

payments table:

------------------------------ | id  | invoice_id  | amount | ------------------------------ |  1  |     5       | 100    | ------------------------------ |  2  |     5       | 290    | ------------------------------ 

desired output:

-------------------------------------- | id  | invoicetotal  | paymenttotal | -------------------------------------- |  1  |     390       |    390       | -------------------------------------- 

the query i've tried

select      i.id,     sum(ii.amount) invoicetotal,     sum(p.amount) paymenttotal      invoices left join      invoice_items ii on i.id = ii.invoice_id left join      payments p on i.id = p.invoice_id      i.student_id = '25' group      i.id 

what seems calculate sum of payments invoice_items.amount appears have been duplicated 6 (which number of payments there are).

i have read similar questions on here , here examples more complex i'm trying , can't figure out put where.

the join causes problem cartesian products. if student has multiple invoice items , payments, totals wrong.

one approach works best invoices union all/group by approach:

select i.id, sum(invoicetotal) invoicetotal, sum(paymenttotal) paymenttotal ((select i.id, 0 invoicetotal, 0 paymenttotal        invoices       ) union       (select ii.invoiceid, sum(ii.amount) invoicetotal, null        invoiceitems ii        group ii.invoiceid       ) union       (select p.invoiceid, 0, sum(p.amount) paymenttotal        payments p        group p.invoiceid       )      ) iip group id; 

for single student, recommend correlated subqueries:

select i.id,        (select sum(ii.amount)         invoiceitems ii         ii.invoiceid = i.id        ) totalamount,        (select sum(p.amount)         payment p         p.invoiceid = i.id        ) paymentamount invoices i.studentid = 25;