SQL duplicate values of records with multiple joins

Question

My query works fine until I add in the estimate tables, where my data duplicates.

Below is my table structure:

Jobs

| ID | JobNumber |
|----|-----------|
|  1 | J200      |
|  2 | J201      |

Job_Invoices

| ID | InvoiceNumber | JobID |
|----|---------------|-------|
| 10 |     I300      |   1   |
| 11 |     I301      |   2   |

Invoice_Accounts

| ID | InvoiceId | AccountID | Amount |
|----|-----------|-----------|--------|
| 23 |   10      |     40    |  200   |
| 24 |   10      |     40    |  300   |
| 25 |   10      |     41    |  100   |
| 26 |   11      |     40    |  100   |

Estimates

| ID | JobID | 
|----|-------|
| 50 |   1   |

Estimate_Accounts

| ID | EstimateID| AccountID | Amount |
|----|-----------|-----------|--------|
| 23 |   50      |     40    |  100   |
| 24 |   50      |     40    |  100   |

Accounts

| ID | Name |
|----|------|
| 40 | Sales|      
| 41 | EXP  |

I am trying the below:

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

This is what I am hoping to achieve:

| JobNumber | Account   | Total Invoiced | Total Estimated |
|-----------|-----------|----------------|-----------------|
| J200      |   EXP     |  100           |    0            |
| J200      |   Sales   |  500           |    200          |
| J201      |   Sales   |  100           |    0            |

This works fine if before I add the Estimates and Estimate_Accounts tables, my result looks like the above (without the Total Estimate) column.

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:

| JobNumber | Account   | Total Invoiced | Total Estimated |
|-----------|-----------|----------------|-----------------|
| J200      |   EXP     |  200           |    0            |
| J200      |   Sales   |  1000          |    400          |
| J201      |   Sales   |  200           |    0            |

This seems likely to be a common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations. [Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs](https://stackoverflow.com/a/45252750/3404097) — philipxy, Oct 19 '19 at 21:14
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/q/4707673/3404097) — philipxy, Oct 19 '19 at 21:16
Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular formatted initialization. — philipxy, Oct 19 '19 at 21:16
Please always tag SQL questions with the DBMS you are using. Which is yours? Oracle? SQL Server? MySQL? PostgreSQL? ... — Thorsten Kettner, Oct 19 '19 at 21:59

GMB · Answer 1 · 2019-10-19T22:05:36.767

There are two tables where duplication may happen:

Invoice_Accounts has several records per AccountID/InvoiceId tuple, that you want to SUM()
Estimate_Accounts has several records per EstimateID/AccountID tuple. Also I think that you should use column AccountID when joining this table: this requires changing the order of the JOINs, so Estimate_Accounts is joined after Accounts

I think that it would be simpler to move the aggregation to subqueries, and then join them in the outer query.

Consider:

SELECT  
    J.JobNumber,
    A.Name AS Account,
    JA.Amount AS Total_Invoiced,
    COALESCE(EA.Amount, 0) AS Total_Estimated
FROM 
    Job J
LEFT JOIN 
    Job_Invoices JI ON JI.JobID = J.ID    
INNER JOIN 
    (
        SELECT AccountID, InvoiceId, SUM(Amount) Amount 
        FROM Invoice_Accounts 
        GROUP BY InvoiceId, AccountID
    ) JA ON JA.InvoiceId = JI.ID
INNER JOIN 
    Accounts A ON A.ID = JA.AccountID
LEFT JOIN 
    Estimates E ON E.JobID = J.ID
LEFT JOIN 
    (
        SELECT EstimateID, AccountID , SUM(Amount) Amount 
        FROM Estimate_Accounts 
        GROUP BY EstimateID, AccountID
    ) EA ON EA.EstimateID = E.ID AND EA.AccountID = JA.AccountID
ORDER BY 
    J.JobNumber, A.Name;

This demo on DB Fiddle with your sample data returns:

| JobNumber | Account | Total_Invoiced | Total_Estimated |
| --------- | ------- | -------------- | --------------- |
| J200      | EXP     | 100            | 0               |
| J200      | Sales   | 500            | 200             |
| J201      | Sales   | 100            | 0               |

@JimDover: I edited my answer, I guess that it does what you want now. — GMB, Oct 19 '19 at 21:35

score 1 · Answer 2 · edited Oct 19 '19 at 21:52

You need to aggregate before joining, because otherwise the JOIN generates a Cartesian product. However, this is complicated by the account information.

So, this approach aggregates the estimates and invoices separately by account and job. It then combines them using UNION ALL and joins in the rest of the information:

SELECT J.JobNumber, A.Name AS Account,
       JE.Total_Invoiced, JE.Total_Estimated
    FROM Job J LEFT JOIN 
         ((SELECT JI.JobId, JA.AccountId, SUM(JA.Amount) AS Total_Invoiced, NULL as Total_Estimated
           FROM Job_Invoices JI  JOIN 
                Invoice_Accounts JA
                ON JA.InvoiceId = JI.ID
           GROUP BY JI.JobId, JA.AccountId
          ) UNION ALL
          (SELECT E.JobId, EA.AccountId, NULL, SUM(EA.Amount) as Total_Estimated
           FROM Estimates E JOIN
                Estimate_Accounts EA
                ON EA.EstimateID = E.ID
           GROUP BY E.JobId, EA.AccountId
          )
         ) JE
         ON JE.JobId = J.ID LEFT JOIN  
         Accounts A
         ON A.ID = JE.AccountID
    ORDER BY J.JobNumber;

score 1 · Accepted Answer · answered Oct 19 '19 at 21:53

You want to join invoice totals with esitimated totals. Both are aggregations. So, make these aggregations, then join. With the jobs and accounts thus found, join the job and account tables.

select
  j.jobnumber,
  a.name as "Account",
  inv.total as "Total Invoiced",
  est.total as "Total Estimated",
from
(
  select e.jobid, ea.accountid, sum(ea.amount) as total
  from estimate_accounts ea
  join estimates e on e.id = ea.estimateid
  group by e.jobid, ea.accountid
) est
full outer join
(
  select ji.jobid, ia.accountid, sum(ia.amount) as total
  from invoice_accounts ia
  join job_invoices ji on ji.id = ia.invoiceid
  group by ji.jobid, ia.accountid
) inv using (jobid, accountid)
join jobs j on j.id = jobid
join accounts a on a.id = accountid
order by j.jobnumber, a.name;

If your DBMS doesn't support the USING clause, you must use ON instead:

select
[...]
) inv on inv.jobid = est.jobid and inv.accountid = est.accountid
join jobs j on j.id in (est.jobid, inv.jobid)
join accounts a on a.id in (est.accountid, inv.accountid)
order by j.jobnumber, a.name;

SQL duplicate values of records with multiple joins

3 Answers3