Return Data from multiple tables

Question

Can anyone help me to fix this SQL query.

Select c.name, c.address, c.area, sum(i.amount) from customers c 
Inner Join invoices i on c.id=i.customer_id 
Inner Join invoice_payments p on i.id = p.invoice_id 
group by c.name

Here is tables structure

Customer Table

id(int), Name(string), Area(string), Address(string)

Invoices Table

id(int), Invoice_Number (string), customer_id(int), amount(decimal)

Invoice_payments

id(int), invoice_id(int), amount(decimal)

I have two payment records of one client in invoice_payments table so it's returning double amount of that client...

How i can fix this issue

Provided query is returning following result.

Answer 1

Calculate separately the 2 sums and join them:

select 
  ci.name, ci.address, ci.area, 
  ci.invoice_amount, cp.invoice_payments_amount 
from (
  select c.id, c.name, c.address, c.area, sum(i.amount) invoice_amount
  from customers c inner join invoices i on c.id = i.customer_id 
  group by c.id, c.name, c.address, c.area
) ci  inner join (
  select c.id, sum(p.amount) invoice_payments_amount 
  from customers c 
  inner join invoices i on c.id = i.customer_id 
  inner join invoice_payments p on i.id = p.invoice_id
  group by c.id
) cp on cp.id = ci.id