title: Duplicated Data When Joining 4 Tables in MySql
body: <p>I have 4 tables, with the relevant columns summarized here:</p>
  
  <pre><code>customers:
    id
    name
  
  credits:
    id
    customer_id   # ie customers.id
    amount
  
  sales:
    id
    customer_id  # ie customers.id
  
  sales_items:
    id
    sale_id      # ie sales.id
    price
    discount
  </code></pre>
  
  <p>The idea is that <code>customers</code> lists all of our customers, <code>credits</code> lists each time they have paid us, <code>sales</code> lists each time they have bought things from us (but not what things they bought) and <code>sales_items</code> lists all of the items they bought at each of those sales.  So you can see that <code>credits</code> and <code>sales</code> both relate back to <code>customers</code>, but <code>sales_items</code> only relates back to <code>sales</code>.</p>
  
  <p>As an example dataset, consider:</p>
  
  <pre><code>customers:
    id  |  name
    5   |  Carter
  
  credits:
    id  |  customer_id  |  amount
    1   |  5            |  100
  
  sales:
    id  |  customer_id 
    3   |  5
  
  sales_items:
    id  |  sale_id  |  price  |  discount
    7   |  3        |  5      |  0
    8   |  3        |  0      |  0
    9   |  3        |  10     |  0
  </code></pre>
  
  <p>I have tried this in MySQL:</p>
  
  <pre><code>SELECT c.*,
    SUM( cr.amount ) AS paid,
    SUM( i.price + i.discount ) AS bought 
  FROM customers AS c
  LEFT JOIN sales       AS s  ON s.customer_id = c.id
  LEFT JOIN sales_items AS i  ON i.sale_id = s.id
  LEFT JOIN credits     AS cr ON cr.customer_id = c.id
  WHERE c.id = 5
  </code></pre>
  
  <p>But it returns:</p>
  
  <pre><code>  id  |  name    |  paid  |  bought
    5   |  Carter  |  300   |  15
  </code></pre>
  
  <p>If I omit the <code>SUM()</code> functions, it returns:</p>
  
  <pre><code>  id  |  name    |  paid  |  bought
    5   |  Carter  |  100   |  5
    5   |  Carter  |  100   |  0
    5   |  Carter  |  100   |  15
  </code></pre>
  
  <p>So it looks like it's returning one row for every record matched in <code>sales_items</code>, but it's filling in the <code>amount</code> column with same value from <code>credits</code> each time.  I see that this is happening, but I'm not understanding why it's happening.</p>
  
  <p>So, two questions:
  <strong>1.</strong> What is happening that it's smearing that one value through all of the rows?
  <strong>2.</strong> What SQL can I throw at MySQL so that I can get this back:</p>
  
  <pre><code>id  |  name    |  paid  |  bought
  5   |  Carter  |  100   |  15
  </code></pre>
  
  <p>I know that I could break it all up in subqueries, but is there a away to do it just with joins?  I was hoping to learn a thing or two about joins as I tackled this problem.  Thank you.</p>
  
  <p><strong>Edit:</strong> I created an SQL Fiddle for this: <a href="http://sqlfiddle.com/#!2/0051b/1/0" rel="nofollow">http://sqlfiddle.com/#!2/0051b/1/0</a></p>