472,144 Members | 1,953 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,144 software developers and data experts.

Compare two tables field by field

i need to compare two tables, field-by-field. The matching will be based on a key field, and the comparison will be for the non-key fields. If there is a mismatch, the key fields, along with the mismatch value (like-> Key, table1.field, table2.field)

can this be done using SQL? Or do we need to write any programs for this?

Dec 26 '13 #1
7 1739
12,516 Expert Mod 8TB
Yes, join on your key and in the WHERE clause, return only the non matches.
Dec 26 '13 #2
I tried that, but if i do that, i will not know which row or which field has the mismatch, unless i got line by line. Let me try to explain with the example below:

Table1 Fld 1 Fld 2 Fld 3
Key 1 A B C
Key 2 A B C
Key 3 A B C

Table2 Fld 1 Fld 2 Fld 3
Key 1 A X C
Key 2 A B Y
Key 3 A B Z

The output I am looking for is something like this:

Key1, Field2, B, X
Key2, Field3, C, Y
Key3, Field3, C, Z

If I just get a list of key fields, then I need to review them row by row to find out which column has a mismatch.
Dec 26 '13 #3
12,516 Expert Mod 8TB
In that case, use a union for each field that you need data returned on.
Dec 27 '13 #4
Thanks for the quick response. I will research on UNION and figure out how to use it. Meanwhile, if you have a sample query, could you pls share? Or makeup a simple SQL with one field. that would help.

Thanks again,
Dec 27 '13 #5
5,501 Expert Mod 4TB
PNair, we usually as that you show your work first. You'd be surprised at how many students like to troll. (^.^)
Dec 27 '13 #6
Here is my query. I had to make some edits to remove the table name and field name, but the structure is same.

As you can see, I am doing a SELECT *, which will give me all rows which has a mismatch. I cannot know which of the 3 conditions failed.

Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. FROM   TABLE1 PD 
  3.        INNER JOIN TABLE2 PM 
  4.                ON PM.KEY = PD.KEY 
  6.     OR PM.FIELD2 <> PD.FIELD2 
  7.     OR PM.FIELD3 <> PD.FIELD3
Dec 27 '13 #7
12,516 Expert Mod 8TB
Please use code tags when posting code or formatted data.

Split it up into multiple queries and use a UNION to join the results.
Dec 27 '13 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Prakash RudraRaju | last post: by
4 posts views Thread by dfs9 | last post: by
2 posts views Thread by sparks | last post: by
7 posts views Thread by Deano | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.