Comparing Tables and finding mismatches

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing Tables and finding mismatches

Author

Topic

saminda
Starting Member

10 Posts

Posted - 2010-08-18 : 21:52:12

Hi friends,

Appreciate any advice/help on the following issues.

There are 2 tables(file & item are keys);

table1
file item name
015 2 EUR
015 2 USD

table2
file item name
015 2 AUD
015 2 CAD
015 2 CHF
015 2 EUR
015 2 USD

I want to match each column of these tables for any data mismatch.
I tried the following SQL,

select distinct 1.file_no , 1.item_no,2.item_value,1.item_value
from table1 as 1
left outer join table2 as 2
on 1.file_no = 2.file_no and 1.item_no = 2.item_no
where (2.item_value<>1.item_value ) or(2.item_value is null and 1.item_value <> '')
or (2.item_value <> '' and 1.item_value is null)
order by 1.file_no,1.item_no

Result I got from this was like
015 2 EUR AUD
015 2 USD CAD
015 2 EUR CHF
015 2 EUR USD
015 2 USD AUD
015 2 USD CAD
015 2 USD CHF
015 2 USD EUR

But I want to get something like below,
015 2 EUR AUD
015 2 USD CAD
015 2 NULL CHF
015 2 NULL EUR
015 2 NULL USD

Since keys are the same, I am not sure whether it is possible to get my desired results. Appreciate if anyone can enlighten me on this.

Thanks in advance
Saminda

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-19 : 03:22:17

Try this -


select 
CASE WHEN [1].file_no IS NULL THEN [2].File_no ELSE [1].file_no END File_no , 
CASE WHEN [1].item_no IS NULL THEN [2].item_no ELSE [1].item_no END item_no ,
[1].item_value, [2].item_value
from table1 as [1] 
Full outer join table2 as [2] 
on [1].file_no = [2].file_no and [1].item_no = [2].item_no and [1].item_value = [2].item_value

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 13:45:53

if you're using SQL 2005 or later, you can use like this


SELECT COALESCE(t1.file,t2.file) AS file,
COALESCE(t1.item,t2.item) AS item,
t1.name,t2.name
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY file,item ORDER BY name) AS Seq,* FROM table1)t1
FULL OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY file,item ORDER BY name) AS Seq,* FROM table2)t2
ON t2.Seq=t1.Seq
AND t2.file=t1.file
AND t2.item = t1.item

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

saminda
Starting Member

10 Posts

Posted - 2010-08-19 : 19:42:03

Hi visakh16

Thanks for the response & advice ! following code gets all the mismatches of the 2 tables (without where condition rows with same values will also be selected).

SELECT COALESCE(t1.file_no,t2.file_no) AS file_no,
COALESCE(t1.item_no,t2.item_no) AS item_no,
t1.name_1,t2.name_1
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY file_no,item_no ORDER BY name_1) AS Seq,* FROM tbl1)t1
FULL OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY file_no,item_no ORDER BY name_1) AS Seq,* FROM tbl2)t2
ON t2.Seq=t1.Seq
AND t2.file_no=t1.file_no
AND t2.item_no = t1.item_no
where (t1.name_1<>t2.name_1 collate japanese_cs_as)
or(t1.name_1 is null and t2.name_1 <> '')
or (t1.name_1 <> '' and t2.name_1 is null)

ras

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 11:11:48

OK. Hope this was what you were after!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-08-20 : 15:59:44

Sorry but those are not keys in the sampel data you gave. Is this what you meant to post?

CREATE TABLE Table1
(file_nbr CHAR(3) NOT NULL,
item_nbr INTEGER NOT NULL,
currency_code CHAR(3) NOT NULL,
PRIMARY KEY (file_nbr, item_nbr, currency_code));

INSERT INTO Table1
VALUES ('015', 2, 'EUR'), ('015', 2, 'USD');

CREATE TABLE Table2
(file_nbr CHAR(3) NOT NULL,
item_nbr INTEGER NOT NULL,
currency_code CHAR(3) NOT NULL,
PRIMARY KEY (file_nbr, item_nbr, currency_code));

INSERT INTO Table2
VALUES ('015', 2, 'AUD'), ('015', 2,' CAD'),
('015', 2, 'CHF'), ('015', 2, 'EUR'), ('015', 2, 'USD');

Now, look up the Set operators and your old set theory book:

SELECT *
FROM (SELECT * FROM Table2
UNION
SELECT * FROM Table1) AS X1
EXCEPT
(SELECT * FROM Table2
INTERSECT
SELECT * FROM Table1)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources