Subject | Re: problem with 3-table join with totals |
---|---|
Author | Svein Erling Tysvær |
Post date | 2007-12-11T13:42:33Z |
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
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