title: SQL JOIN, GROUP BY on three tables to get totals
body: <p>I've inherited the following DB design.  Tables are:</p>
  
  <pre><code>customers
  ---------
  customerid  
  customernumber
  
  invoices
  --------
  invoiceid  
  amount
  
  invoicepayments
  ---------------
  invoicepaymentid  
  invoiceid  
  paymentid
  
  payments
  --------
  paymentid  
  customerid  
  amount
  </code></pre>
  
  <p>My query needs to return invoiceid, the invoice amount (in the invoices table), and the amount due (invoice amount minus any payments that have been made towards the invoice) for a given customernumber.  A customer may have multiple invoices.</p>
  
  <p>The following query gives me duplicate records when multiple payments are made to an invoice:</p>
  
  <pre><code>SELECT i.invoiceid, i.amount, i.amount - p.amount AS amountdue
  FROM invoices i
  LEFT JOIN invoicepayments ip ON i.invoiceid = ip.invoiceid
  LEFT JOIN payments p ON ip.paymentid = p.paymentid
  LEFT JOIN customers c ON p.customerid = c.customerid
  WHERE c.customernumber = '100'
  </code></pre>
  
  <p>How can I solve this?</p>