i have 3 tables:
create table t1 (id I))
create table t2 (id I,fid i,amount n(10,2)
create table t3 (id I,fid i,amount n(10,2)

the following sql returns the wrong sum:
select dist t1.*,sum(t2.amount) as n2,sum(t3.amount) as n3 ;
from t1 ;
inner join t2 on t1.id=t2.fid;
inner join t3 on t1.id=t3.fid ;
group by t1.id

data:

t1.id=1
t2.id=100,t2.fid=1,t2.amount=50
t2.id=101,t2.fid=1,t2.amount=60
t2.id=102,t2.fid=1,t2.amount=40

t3.id=98,t3.fid=1,t3.amount=10
t3.id=99,t3.fid=1,t3.amount=15

we expect the n2 to be 150 and n3 to be 25

but we get n2=300, n3=75
notice each sum is the real sum* the count of the other record.
what am I doing wrong??


Thanks in advance, see ya in NAWLINS

Peter