Subject Re: problem with 3-table join with totals
Author Svein Erling Tysvær
I don't quite understand what you write, but I think I know the
answer! Say you have these tables:

T1
PK Value
1 1

T2
PK1 Value
1 1
1 1

T3
PK1 Value
1 1
1 1
1 1

What would you expect

SELECT sum(T1.Value)
FROM T1
JOIN T2 on T1.PK = T2.PK1
JOIN T3 on T1.PK = T3.PK1

to return?

I'd expect the answer to be 6, since aggregate results should be
calculated based on the result set (in my opinion), and if I had used
T1.Value rather than sum(T1.Value) I'd get a result set consisting of
six rows.

If you want something different, I'd say you either have to go for
subselects or separate selects (or possibly UNION). Though note that
I haven't tried anything, and my expectations about what Firebird
returns could be wrong.

HTH,
Set