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):
| SO.No_ |
|---|
| SO2101234 |
| SO2101235 |
| SO2101237 |
| SO2101239 |
PR (PreRecording):
| PR.No_ | PR.Line | PR.Amount |
|---|---|---|
| SO2101234 | 1 | 50 |
| SO2101234 | 2 | 18 |
| SO2101239 | 1 | 110 |
| SO2101239 | 2 | 40 |
| SO2101239 | 3 | 40 |
IH (InvoiceHeader):
| IH.No_ | IH.SO |
|---|---|
| VF2134889 | SO2101234 |
| VF2134890 | SO2101235 |
| VF2134891 | SO2101239 |
| VF2134892 | SO2101234 |
| VF2134893 | SO2101239 |
IL (InvoiceLine):
| IL.DocNo_ | IL.LineNo | IL.LineAmount |
|---|---|---|
| VF2134889 | 1 | 55 |
| VF2134889 | 2 | 23 |
| VF2134890 | 1 | 12 |
| VF2134890 | 2 | 34 |
| VF2134890 | 3 | 87 |
| VF2134891 | 1 | 114 |
| VF2134892 | 1 | 53 |
| VF2134892 | 2 | 25 |
| VF2134892 | 3 | 17 |
| VF2134892 | 4 | 15 |
| VF2134893 | 1 | 43 |
| VF2134893 | 2 | 38 |
Relations:
PR.No_=SO.No_IH.SO=SO.No_IL.DocNo_=IH.No_
Desired outcome:
| SO.No_ | PR.SumAmount | IH.SumAmount |
|---|---|---|
| SO2101234 | 68 | 188 |
| SO2101235 | 0 | 133 |
| SO2101237 | 0 | 0 |
| SO2101239 | 190 | 195 |
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....