Duplication of results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplication of results

Author

Topic

dg19
Starting Member

15 Posts

Posted - 2010-09-20 : 04:55:44

Dear All,

I have the following query

select invoicenum,client_no,name_client,debtor_no,name_debtor,doc_no,doc_date,due_date,doc_amount,doc_bal,doctype from opditm
inner join tempmatchtable on opditm.doc_no like '%' + invoicenum + '%'
inner join client on opditm.client_no=client.code_client
inner join debtor on opditm.debtor_no=debtor.code_debtor
where exists( select invoicenum,rem_amount,rem_date from tempmatchtable
where id like 'HSS%' and opditm.doc_no like '%' + invoicenum + '%' and rem_amount=opditm.doc_bal and opditm.doc_bal = opditm.doc_amount and opditm.doctype in (1,21) and opditm.apwhst<>21  and opditm.client_no=343)

which will compare the information of table "tempmatchtable" to table "opditm" and will return all that exists in both.

The invoicenum may be in the form "A123456" or "1234567".

The problem:-

when i run the above query with invoices 13,3,1.

I get the same information thrice.

Please advice on how to cater for this type of problem.

Regards,
Dwij

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-20 : 05:00:34

Check your join conditions. Run your query piece by piece for the 3 invoices to find out where your join condition is matching 3 rows.

dg19
Starting Member

15 Posts

Posted - 2010-09-20 : 05:52:24

Dear sakets_2000,

I have tested the conditions, the query is matching 3 row at

inner join tempmatchtable on opditm.doc_no like '%' + invoicenum + '%'

but the line

where exists( select invoicenum,rem_amount,rem_date from tempmatchtable
where id like 'HSS%' and opditm.doc_no like '%' + invoicenum + '%' and rem_amount=opditm.doc_bal and opditm.doc_bal = opditm.doc_amount and opditm.doctype in (1,21) and opditm.apwhst<>21  and opditm.client_no=343)

verifies the amount against the balance in the table opditm.
The invoices have different amount.

Please advice,

Dwij

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-20 : 07:25:33

Its hard to say for certain without looking at data.
Do you mean all invoices have difference rem_amount and exists should match only for a single row?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources