Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have two table (1:n), I want to join it. The first table contains the "balance" (1 for key) the second table contains the movements (n for key)
1) f I use LEFT JOIN, I have the field SALDO multiplied for the n-movements.
OWNER | SUM(SALDO) | SUM(MOV) |
XYZ | 9000 | 1900 |
XYT | 3000 | 500 |
XYR | -2000 | 0 |
2) Instead, if I use LEFT KEEP, or if I don't write any JOIN, working with two table in association, the filed SALDO is correct (not multiplicated by n-movements)
OWNER | SUM(SALDO) | SUM(MOV) |
XYZ | 3000 | 1900 |
XYT | 3000 | 500 |
XYR | -2000 | 0 |
Could someone explain to me in detail why?
Thank you
Andrea
---------
TAB_SALDI:
Load
CONTO AS CONTO_KEY,
CONTO,
DIV,
OWNER,
SALDO
Inline
[
CONTO,OWNER,DIV,SALDO
10001EUR,XYZ,EUR,1000
10002EUR,XYZ,EUR,2000
10003USD,XYT,USD,3000
10004USD,XYR,USD,-2000
];
LEFT JOIN (TAB_SALDI) // LEFT KEEP (TAB_SALDI)
TAB_MOV:
Load
CONTO AS CONTO_KEY,
ID,
MOV
inline
[
CONTO,ID,MOV
10001EUR,1,200
10001EUR,2,100
10001EUR,3,600
10002EUR,1,200
10002EUR,2,300
10002EUR,3,500
10003USD,1,500
];
Hi.
Imagine that you have both tables in Excel.
1) With this approach you construct common table, duplicating the rows from 'balance' table.
As you see, your saldo is copied n-times. As a result the sum is n-times higher.
2) Here you dont copy rows, you lookup for the values to 'balance table or to 'movement' table.
The sum is calculated correctly as you don't copy the rows.
Hi.
Imagine that you have both tables in Excel.
1) With this approach you construct common table, duplicating the rows from 'balance' table.
As you see, your saldo is copied n-times. As a result the sum is n-times higher.
2) Here you dont copy rows, you lookup for the values to 'balance table or to 'movement' table.
The sum is calculated correctly as you don't copy the rows.