Subject Re: Wrong summation within join
Author Svein Erling Tysvær
--- In, "Frank" wrote:
> thanks, that did the trick! The results are what I want them to be
> and they are calculated even faster.

Oops, I didn't intend to write anything that worked faster. But, OUTER
JOINs (in your case LEFT JOIN) can be pretty expensive so it is no big

> But I still don't understand the way the sums are calculated. If the
> result set is like you have described, shouldn't ValueB and ValueC
> have 'wrong' values?

No, but they would be repeated. E.g. if you added

INSERT INTO TableC VALUES(2, 1, '2.0');

your result set would be four rows:

1, 'Sample', 1.0, 1
1, 'Sample', 2.0, 1
1, 'Sample', 1.0, 1
1, 'Sample', 2.0, 1

i.e. each record of TableB joined with each record of TableC.

> btw. I'm happy that the reason for the wrong values lies on my side.

Well, I hope so too. Though it has never been any guarantee that
behaving like I find sensible corresponds to the SQL standard.