469,091 Members | 1,219 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,091 developers. It's quick & easy.

Compare two tables field by field

4
Hi,
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?

GP
Dec 26 '13 #1
7 1575
Rabbit
12,516 Expert Mod 8TB
Yes, join on your key and in the WHERE clause, return only the non matches.
Dec 26 '13 #2
PNair
4
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
Rabbit
12,516 Expert Mod 8TB
In that case, use a union for each field that you need data returned on.
Dec 27 '13 #4
PNair
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
zmbd
5,400 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
PNair
4
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 
  5. WHERE  PM.FIELD1 <> PD.FIELD1 
  6.     OR PM.FIELD2 <> PD.FIELD2 
  7.     OR PM.FIELD3 <> PD.FIELD3
Dec 27 '13 #7
Rabbit
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
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.