LEFT JOIN - LEFT KEEP - ASSOCIATION

[andrew001]

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

];

[whiteline · Accepted solution]

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.