| Comparing Two Tables - pls help [message #35627] |
Tue, 02 October 2001 07:31  |
Prabha R
Messages: 21
Registered: October 2001
|
Junior Member |
|
|
Hi,
I have two tables A and B like :
Table A
-------
Payee Name
Payee Number
Payee City
Payee State
Payee Zip5
Payee Zip4
Payee Zip2
Payee Phone
Payee Country Code.
Table B
-------
Payee Name
Payee Number
Payee City
Payee State
Payee Merchant Number
Payee Phone
Payee Zipcode
The fields in Table B which matches with Table A should have same contents in both the tables. I need to verify this and list out the rows which does not satisfy this criteria.
Since there are around 11 columns which is present in both A and B, if i use the foll. query:
select count(*) from A where not exists (select Payee_Name from B where A.Payee_Name= B.Payee_Name and A.Payee_Number=B.Payee_Number and etc....
I need to write 10 and statements which i think is not efficient...
Could any of you suggest me another efficent way to achieve this?
----------------------------------------------------------------------
|
|
|
|
| Re: Comparing Two Tables - pls help [message #35629 is a reply to message #35627] |
Tue, 02 October 2001 08:07  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
That _is_ an efficient way of performing that query (assuming the appropriate indexes are in place - probably on payee_number). And you aren't writing 10 or 11 _statements_, you are putting together one query with 10 or 11 conditions in the WHERE clause - big difference.
----------------------------------------------------------------------
|
|
|
|
| Re: Comparing Two Tables - pls help [message #35631 is a reply to message #35627] |
Tue, 02 October 2001 08:32 |
Hans
Messages: 42
Registered: September 2000
|
Member |
|
|
another option is to use the minus operator
select a_1, a_2, a_3, .. , a_n from a
minus
select b_1, b_2, b_3, .. , b_n from b
but remember that the minus operator eliminates duplicates.
----------------------------------------------------------------------
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
