Comparing two tables - 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

Comparing two tables

Author

Topic

dg19
Starting Member

15 Posts

Posted - 2010-08-28 : 04:15:00

Hi,

I have writte a Query to compare two table and return me the matched values, its been working on wel, until i received a valu that not exactly the same in both table, for e.g: In my first table the value of doc_no is A33596 and in the second the value of invoicenum is 33596. my code is:

select client_no,name_client,debtor_no,name_debtor,doc_no,doc_date,doc_amount,doc_bal,apwhst from opditm
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 invoicenum=opditm.doc_no and rem_amount=opditm.doc_bal and rem_date=opditm.doc_date and opditm.doctype in (1,21) and opditm.apwhst<>21)

i have tried to use "like" instead of "Equal"(=) (bold) but no result is displayed and i am sure the values exists.

Please help.
regards,
Amit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-28 : 04:54:48

make it opditm.doc_no like '%' + invoicenum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dg19
Starting Member

15 Posts

Posted - 2010-08-28 : 05:08:53

hi visakh16,

Unfortunately cannot test right now, Will give you feedback on the result as soon as possible.

Thanks and regards,

Amit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-28 : 07:44:03

ok. thats fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dg19
Starting Member

15 Posts

Posted - 2010-08-30 : 04:49:40

Hi visakh16,

I tested the solution you provided and it is working like a charm.

Thanks and regards,

Amit

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 05:02:04

I so hope you are not getting false positives, like checking
doc_no A12345 against invoicenum 45. Or 345. Or 2345...

N 56°04'39.26"
E 12°55'05.63"

dg19
Starting Member

15 Posts

Posted - 2010-08-30 : 05:10:28

quote:
Originally posted by Peso

I so hope you are not getting false positives, like checking
doc_no A12345 against invoicenum 45. Or 345. Or 2345...

N 56°04'39.26"
E 12°55'05.63"

Hi Perso,

Thanks for pointing this out.

with the data i am actually testing this problem did not occur, will test run against other values and let you know.

Thanks and regards,

Amit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-30 : 09:50:57

quote:
Originally posted by dg19

Hi visakh16,

I tested the solution you provided and it is working like a charm.

Thanks and regards,

Amit

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources