How to get diff?

[ssivapra]

Anyone knows how to retrive difference information( changed rows , deleted rows , modified rows ) from 2 different tables with the same table strcuture ( same rows and same primary key ). Basically a similar kind of information generated at 2 differnt time interval and stored in 2 different table. I want to make a comparision what are the rows deleted , added and modified in those two tables.help me in this...

Thanks,

[Cakkie]

This gives you all the records from table1 that are not in table2, and all the records from table2 that are not in table1. However, you can't tell if those records were modified, added, or deleted. You can just see that the have changed.
Note: this is 1 SQL statement, presuming that there are two tables (table1 and table2) which have a two-columnd key (combinatin of field1 and field2)

SELECT * FROM table1 WHERE not(field1 in (
SELECT table1.field1 FROM table1
INNER JOIN table2 on table1.field1 = table2.field1 and tabel1.field2 = table2.field2
)) AND not(field2 in (
SELECT table1.field1 FROM table1
INNER JOIN table2 on table1.field1 = table2.field1 and tabel1.field2 = table2.field2
))

UNION

SELECT * FROM table2 WHERE not(field1 in (
SELECT table1.field1 FROM table1
INNER JOIN table2 on table1.field1 = table2.field1 and tabel1.field2 = table2.field2
)) AND not(field2 in (
SELECT table1.field1 FROM table1
INNER JOIN table2 on table1.field1 = table2.field1 and tabel1.field2 = table2.field2
))




Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Iouri]

Another example

I need all Customer ID in the Table T1 that are not in the table T2

SELECT DISTINCTROW [T1].[CustomerID]
FROM T1 LEFT JOIN T2 ON
[T1].[CustomerID] = [T2].[CustomerID]
WHERE ([T2].[CustomerID] Is Null);


Iouri Boutchkine
iouri@hotsheet.com