title: MYSQL subquery or left join?
body: <p>I'm making a little invoice application and I'm stuck on a query to get the total open amount of my invoices. </p>
  
  <p>A little explanation:</p>
  
  <p>I've got 2 tables in the database, <code>invoices</code> and <code>payments</code> which are related on the column <code>invoice_id</code>. It's possible to have multiple payments for one invoice.</p>
  
  <p>What I want to get is the total open amount for all the invoices.</p>
  
  <p>This is what I tried but it doesn't work.</p>
  
  <p>The total_paid amount is not correct, its the sum of the last invoice and not of all invoices.</p>
  
  <pre><code>SELECT
    COUNT( * ) AS count,
    SUM( invoice_amount_total ) AS total,
    (SELECT
       SUM( amount ) AS total
     FROM `invoice_payments`
     WHERE `invoice_id` = s.id) AS total_paid
  FROM sales_invoices AS s
  WHERE s.administration_id = 3
      AND (`invoice_amount_total` &lt;&gt; (SELECT
                        SUM( `amount` ) AS sum
                      FROM `invoice_payments`
                      WHERE `invoice_id` = s.id)
        OR (SELECT
          SUM( amount ) AS sum
            FROM `invoice_payments`
            WHERE `invoice_id` = s.id)IS NULL)
  </code></pre>
  
  <p>Any ideas?</p>