title: SQL duplicate values of records with multiple joins
body: <p>My query works fine until I add in the estimate tables, where my data duplicates.</p>
  
  <p>Below is my table structure:</p>
  
  <p><strong>Jobs</strong></p>
  
  <pre><code>| ID | JobNumber |
  |----|-----------|
  |  1 | J200      |
  |  2 | J201      |
  </code></pre>
  
  <p><strong>Job_Invoices</strong></p>
  
  <pre><code>| ID | InvoiceNumber | JobID |
  |----|---------------|-------|
  | 10 |     I300      |   1   |
  | 11 |     I301      |   2   |
  </code></pre>
  
  <p><strong>Invoice_Accounts</strong></p>
  
  <pre><code>| ID | InvoiceId | AccountID | Amount |
  |----|-----------|-----------|--------|
  | 23 |   10      |     40    |  200   |
  | 24 |   10      |     40    |  300   |
  | 25 |   10      |     41    |  100   |
  | 26 |   11      |     40    |  100   |
  </code></pre>
  
  <p><strong>Estimates</strong></p>
  
  <pre><code>| ID | JobID | 
  |----|-------|
  | 50 |   1   | 
  </code></pre>
  
  <p><strong>Estimate_Accounts</strong></p>
  
  <pre><code>| ID | EstimateID| AccountID | Amount |
  |----|-----------|-----------|--------|
  | 23 |   50      |     40    |  100   |
  | 24 |   50      |     40    |  100   |
  </code></pre>
  
  <p><strong>Accounts</strong></p>
  
  <pre><code>| ID | Name |
  |----|------|
  | 40 | Sales|      
  | 41 | EXP  |
  </code></pre>
  
  <p>I am trying the below:</p>
  
  <pre><code>SELECT  
      J.JobNumber,
      A.Name AS "Account",
      SUM(JA.Amount) AS 'Total Invoiced',
      SUM(EA.Amount) AS 'Total Estimated',
  FROM 
      Job J
  LEFT JOIN 
      Job_Invoices JI ON JI.JobID = J.ID    
  LEFT JOIN
      Estimates E ON E.JobID = J.ID
  LEFT JOIN
      Estimate_Accounts EA ON EA.EstimateID = E.ID
  INNER JOIN 
      Invoice_Accounts JA ON JA.InvoiceId = JI.ID
  INNER JOIN 
      Accounts A ON A.ID = JA.AccountID
  GROUP BY 
      J.JobNumber, A.Name, JA.Amount
  ORDER BY 
      J.JobNumber
  </code></pre>
  
  <p>This is what I am hoping to achieve:</p>
  
  <pre><code>| JobNumber | Account   | Total Invoiced | Total Estimated |
  |-----------|-----------|----------------|-----------------|
  | J200      |   EXP     |  100           |    0            |
  | J200      |   Sales   |  500           |    200          |
  | J201      |   Sales   |  100           |    0            |
  </code></pre>
  
  <p>This works fine if before I add the Estimates and Estimate_Accounts tables, my result looks like the above (without the Total Estimate) column.</p>
  
  <p>However, when I try adding the Total Estimated column by joining the Estimates and Estimate_Accounts tables, Total Invoiced and Total Estimated values double, to something like this:</p>
  
  <pre><code>| JobNumber | Account   | Total Invoiced | Total Estimated |
  |-----------|-----------|----------------|-----------------|
  | J200      |   EXP     |  200           |    0            |
  | J200      |   Sales   |  1000          |    400          |
  | J201      |   Sales   |  200           |    0            |
  </code></pre>