General information
Category:
Coding, syntax & commands
>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.
No... since I don't know what is in field one or two or three it is really hard to see how you have things joined.. also, I don't know if you are wanting certain fields to be empty or those are just " marks to short step typeing what is above it...
Here is a tip... Turn of the grouping and you will see all the records that are created in your query... chances are that records are getting included twice from table3 that you didn't intend.
Rember, if you select X from table 1 and there are many records in table 2 that match from your join, and you sum(x) in will include X from table 1 for every matching record in table 2 overstating the sum by the number of records in which the join was true.
I believe you can elimination the from happening if you do something other than the default join... I always get those confused, left outter or right outter or some such.
BOb
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only