duplicate records in join 
Author Message
 duplicate records in join

I have two tables, with the following fields:
1) Table X:  RecordNumber, Account Code,  amount
2) Table Y:  Account Code, Description

I wish to list the total amount for each account code and get the account
code description:
i.e.:   Account   Description     Total
           A         AccountA        2,000
           B         AccountB        1,000
           C         AccountC        3,000

I execute the following query:

Select X.Account, sum(X.amount) as Tot, Y.Description
from TableX X, TableY Y
where X.Account=Y.Account
group by X.Account, Y.Description

For each account I get an ampunt which is the correct amount times 2 (in
fact, if I run the same query without the "group by" statement, the same
account appears twice in the result set !

Does anybody understand why ?

Note: Tables are DBase IV.



Thu, 13 Nov 2003 06:11:57 GMT  
 duplicate records in join
No idea why every record appears twice, but you can avoid it by adding
DISTINCT or DISTINCTROW after the SELECT.



Quote:
> I have two tables, with the following fields:
> 1) Table X:  RecordNumber, Account Code,  amount
> 2) Table Y:  Account Code, Description

> I wish to list the total amount for each account code and get the account
> code description:
> i.e.:   Account   Description     Total
>            A         AccountA        2,000
>            B         AccountB        1,000
>            C         AccountC        3,000

> I execute the following query:

> Select X.Account, sum(X.amount) as Tot, Y.Description
> from TableX X, TableY Y
> where X.Account=Y.Account
> group by X.Account, Y.Description

> For each account I get an ampunt which is the correct amount times 2 (in
> fact, if I run the same query without the "group by" statement, the same
> account appears twice in the result set !

> Does anybody understand why ?

> Note: Tables are DBase IV.



Fri, 14 Nov 2003 15:51:05 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Duplicate Error on insert record

2. Handling Duplicate records

3. Using SQL to delete duplicate records

4. Duplicate records

5. Tracking duplicate records?

6. Duplicate records in Interbase

7. Duplicating Records

8. Duplicating records D4 & Paradox

9. How to Duplicate Records with TTable Component

10. duplicate records in TDBGrid

11. append records without duplicates keys

12. Duplicate record problem D3 C/S

 

 
Powered by phpBB® Forum Software