Comparing two tables and find mismatch data

[JVZ]

Could someone please help with this problem.

I needs some help in writing a SQL statement that will compare two table (that are suppose to hold the same information - trying to perform a true up) and send back only the rows that contain different data...Can this be done w/SQL? or will I have to write some code to perform this task...

Thanks in advance for you help...

 

[sdtucker]

This is the easiest way (depending on your needs you may want to build this both directions (I'll show you))

'This will test for all values in table A that are not in B
SELECT A.ID, A.Description, B.ID
FROM A
Left outer join B on A.ID = B.ID
WHERE B.ID is null

(Now the other direction)

'This will test for all values in table B that are not in A
SELECT B.ID, B.Description, A.ID
FROM B
Left outer join A on A.ID = B.ID
WHERE A.ID is null

Hopefully this is what you need.

ST

 

[JVZ]

Yes that does help, and thank you for thanks for replying...one last question...sorry..

Let say I have these two table:

table A:

id Desc amount
1 Retail 300.00
2 Retail 400.00
3 wholesale 500.00

table B:

id Desc amount
1 Retail 300.00
2 Retail 400.00
3 Retail 500.00

Using your example:
'This will test for all values in table B that are not in A
SELECT B.ID, B.Desc, A.ID, A.Desc
FROM B
Left outer join A on A.ID = B.ID
WHERE B.Desc != A.Desc

Would that work in SQL Server?

 

[tlbroadbent]

Use an INNER JOIN in this case.

SELECT B.ID, B.Desc, A.ID, A.Desc
FROM B
INNER JOIN A
ON A.ID = B.ID
WHERE B.Desc != A.Desc
If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm