Compare two tables query result

dear all

i need to write SQL Query that compares 2 tables as folows:

lets say i have:

table A with A.A, A.B, A.C columns (Table A with Columns A,B,C)

table B with B.A, B.B, B.C columns (Table B with Columns A,B,C)

i need to compare and to mark (get in result Query) each row lets say in table B that was changed from table A

For Example

Table A

A B C

12 15 hello

15 17 adv

asd 19 23

14 rer 89

Table B

A B C

12 15 hello

15 19 adv

*** 19 23

14 rer 89

the result is the records which was changed from A to B

A B C

15 19 adv

*** 19 23

Thnks alot!

That is probably non-trivial in terms of performance:

Select

CASE WHEN A.A != A.A THEN '***' ELSE A.A AS A
CASE WHEN A.B != A.B THEN '***' ELSE A.B AS B

CASE WHEN A.C != A.C THEN '***' ELSE A.C AS C
FROM TableA
INNER Join TableB
ON A.IdentifierWhichcannotchange = b.IdentifierWhichcannotchange

You will need the IdentifierWhichcannotchange as elsewhere you cannot identify the matching rows, e.g. how to know that the if two rows look the same after the change from which rows they were actually sourced on.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

can you explain in more details the A.IdentifierWhichCannotchange, B.IdentifierWhichCannotchange?

the query result dows not change anything on the tables

p.s i run the query in msAccess

And very important thing that i forgotten to mention

The firsrt 3 columns are my Unique (Primary Key)

|||

You need an identitfier in both tables which cannot change, otherwise you won′t be able to match the rows. its like building a sum from multiple values and then afterwards trying to identity which values the sum is based on. In your case, if you have two rows containg the values

1;23;27
3;23;59

which are changed to

4 (changed);23;54 (changed)
4 (changed);23;55 (changed)

How would you know which row refers to which in the other table. they have lost their identifying attribute here (the combination of values). if you had some identifying value which is not supposed to change then you can easily compare the values in the rows refering to the non-changeable attribute (like I did in the query above)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

OK

first thanks for the replies

so here is my tables and data:

t04 A B C D E F H I J 019 003 000028 1 004604070 1 06/05/2007 01/01/2001 003 019 003 000036 1 004604070 1 06/05/2007 01/01/2001 003 019 003 000044 1 030557268 2 06/05/2007 01/01/2001 003 t05 A B C D E F H I J 019 001 000004 1 028354470 1 15/05/2007 28/02/2002 001 019 003 000028 1 004604070 1 06/05/2007 31/03/1986 003 019 003 000036 1 004604070 1 06/05/2007 31/12/1989 002 019 003 000044 1 030557268 2 06/05/2007 31/03/1986 003 result A B C D E F H I J 019 001 000004 1 028354470 1 15/05/2007 28/02/2002 001 019 003 000036 1 004604070 1 06/05/2007 31/12/1989 002

my Id is Columns A,B,C

in the result we can notice 2 things

1) 019 001 000004 was added because it exists in t05 and not t04

2) the value in col j for 019 003 000036 was changed from 003 to 002

the goal is to create table of diffrences!

in general i can unified columns A,B,C to one column (if it helps) 019001000004 for examle

i quess i clarified my self better

|||

That will be something like:

Select

CASE WHEN A.D != A.D THEN '***' ELSE A.D AS D,

CASE WHEN A.E != A.E THEN '***' ELSE A.E AS E,

CASE WHEN A.F != A.F THEN '***' ELSE A.F AS F,

CASE WHEN A.H != A.H THEN '***' ELSE A.H AS H,

CASE WHEN A.I != A.I THEN '***' ELSE A.I AS I,

CASE WHEN A.J != A.JTHEN '***' ELSE A.J AS J

FROM TableA A
INNER Join TableB B
ON

A.A = B.A AND

A.B = B.B AND
A.C = C.C

Jens K. Suessmeyer

http://www.sqlserver2005.de