Compare two tables field by field

[PNair]

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

[Rabbit]

Yes, join on your key and in the WHERE clause, return only the non matches.

[PNair]

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.

[Rabbit]

In that case, use a union for each field that you need data returned on.

[PNair]

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,

[zmbd]

PNair, we usually as that you show your work first. You'd be surprised at how many students like to troll. (^.^)

[PNair]

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.

SELECT * 
FROM   TABLE1 PD 
       INNER JOIN TABLE2 PM 
               ON PM.KEY = PD.KEY 
WHERE  PM.FIELD1 <> PD.FIELD1 
    OR PM.FIELD2 <> PD.FIELD2 
    OR PM.FIELD3 <> PD.FIELD3

[Rabbit]

Please use code tags when posting code or formatted data.

Split it up into multiple queries and use a UNION to join the results.