>Hi all,
>
>I'm experiencing a pretty odd anomoly using a SQL statement. The syntax for this query is as follows :
>
>SELECT table1.field1,table1.field2,table1.field3,sum(table2.field1)as newfield1, ;
> table2.field2,(sum(table2.field1)+table2.field2) as newfield2,sum(table3.field1) as newfield3 ;
> FROM table1,table2,table3 ;
> WHERE table2.field3 == table1.field1 ;
> AND table3.field2 == table1.field1 ;
> AND table2.field4 == "
" ;
> AND table3.field3 == "" ;
> AND table1.field4 = " ;
> GROUP BY table1.field1 ;
> ORDER BY table1.field1
>
>My problem is that the sum of newfield3 is always twice as much as what is actually in table3.field1. Can anyone explain why this may be happening ?
>
>TIA
>
>Stacey Wilson
>Reinsurance Management Inc.
It's by chance. It could be triple or quadraple with this SQL depending how many match is found in table2. In table3, record selected and summed for every matching count in table2. If table2 has 4 records for a particular table1.field1 == table2.field3 then table3 would have quadraple value. Moreover if table3 has 2 matching records for that particular table1.field1 then table2 would be doubled and table3 would be 4*2. This is the worst case joining that should never be done (if table2 has 1000 recs and table3 1000 w/o sum you would get 1000000 recs in result). To do it one SQL you could sum the table3 via a functionSELECT table1.field1,table1.field2,table1.field3,sum(table2.field1)as newfield1, ;
table2.field2,(sum(table2.field1)+table2.field2) as newfield2,t3sum(table1.field1) as newfield3 ;
FROM table1,table2 ;
WHERE table2.field3 == table1.field1 ;
AND table2.field4 == "<some value>" ;
AND table1.field4 = "<some value> ;
GROUP BY table1.field1 ;
ORDER BY table1.field1
function t3sum
lparameters t1f1
SELECT sum(table3.field1) as sumt3 ;
from t3 table3 ;
where table3.field2 == t1f1 ;
AND table3.field3 == "<some value>" ;
AND table1.field4 = "<some value> ;
into array t3sum
return iif(type("t3sum")="U",0,t3sum[1])Of course this is not the correct way doing it but to show you what's going on. Secondly, in this SQL table2.field2 and sum(table2.field2)+field2 are meaningless because they would be related with the last matching record for every table1.field1 value.
Cetin
