After driving myself crazy by countless attempts, I turn to you and hope someone is willing to help me....

I have 4 tables: SO (ServiceOrder), PR (PreRecording), IH (InvoiceHeader) and IL (InvoiceLine), with the columns and rows like underneath:

SO (ServiceOrder):

PR (PreRecording):

IH (InvoiceHeader):

IL (InvoiceLine):

Relations:

• PR.No_ = SO.No_
• IH.SO = SO.No_
• IL.DocNo_ = IH.No_

Desired outcome:

With my usual simple inner/outer/joins I don't succeed, it's clear that some SELECT's should be nested. With a simple query to combine SO and PR, I have no problem. With a simple query to combine SO and IH, I have no problem. However, when I try to setup a query to get all of this in one output, it gets messed up and values start to multiply due to the rows that are used/found in the several tables.

Many thanks in advance....

CodePudding user response：

A simple strategy is to flatten the result before doing other lookups. I don't fully grasp what you need to return here but this is the general idea.

with SOPR as (
    /* first-level aggregated results */
    select SO.No_, sum(PR.Amount) as PRAmount
    from SO inner join PR on PR.No_ = SO.No_
    group by SO.No_
)
select
    SOPR.No_, min(SOPR.PRAmount) as PRAmount,
    sum(IL.Amount) as ILAmount
/* now look up next level of details */
from SOPR
    inner join IH on IH.SO = SOPR.No_
    inner join IL on IL.DocNo_ = IH.No_
group by SOPR.No_

CodePudding user response：

You can simply nest your queries so that you do the outer SELECT from a flat inner SELECT. This will also work if your RDBMS version does not support WITH CTEs.

To preserve the rows with NULL values, I used LEFT JOINS and additionally the COALESCE function to get 0 as result instead of NULL, as in your desired outcome.

   SELECT PRGRP.No_, 
          COALESCE(PRGRP.SumAmount, 0) AS PRAmount,
          COALESCE(SUM(IL.LineAmount), 0) AS ILAmount
     FROM (   SELECT SO.No_, SUM(PR.Amount) AS SumAmount
                FROM ServiceOrder SO 
           LEFT JOIN PreRecording PR 
                  ON PR.No_ = SO.No_
            GROUP BY SO.No_
          ) PRGRP
LEFT JOIN InvoiceHeader IH 
       ON IH.SO = PRGRP.No_
LEFT JOIN InvoiceLine IL 
       ON IL.DocNo_ = IH.No_
 GROUP BY PRGRP.No_

Result:

Full MySQL example in this db<>fiddle.