Row Comparision of 2 Tables

Question

Post reply

Row Comparision of 2 Tables

tokyo_dude

Newbie

Points: 4
More actions
August 18, 2010 at 11:17 pm

#236832

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 compare each row of these tables and select all the mismatches.
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(compare each row according to its order),
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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

can you explain the output logic, what you are trying to get ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

shield_21 Ten Centuries Points: 1033 More actions · Answer 2

Hi there,

I just noticed that item_value are arranged in ascending order both for table1 and table2.. This will work provided that you intended to sort first the item_value then compare the 2 tables row by row..

DECLARE @table1 TABLE (file_no VARCHAR(10),item_no INT, item_value VARCHAR(50))

DECLARE @table2 TABLE (file_no VARCHAR(10),item_no INT, item_value VARCHAR(50))

INSERT INTO @table1

SELECT '015', 2, 'EUR' UNION ALL

SELECT '015', 2, 'USD'

INSERT INTO @table2

SELECT '015', 2, 'AUD' UNION ALL

SELECT '015', 2, 'CAD' UNION ALL

SELECT '015', 2, 'CHF' UNION ALL

SELECT '015', 2, 'EUR' UNION ALL

SELECT '015', 2, 'USD'

;WITH table1 AS

(

SELECT *,ROW_NUMBER() OVER (PARTITION BY file_no,item_no ORDER BY item_value) AS rn

FROM @table1

),

table2 AS

(

SELECT *,ROW_NUMBER() OVER (PARTITION BY file_no,item_no ORDER BY item_value) AS rn

FROM @table2

)

SELECT t2.file_no,t2.item_no,t1.item_value,t2.item_value

FROM table1 t1

RIGHT JOIN table2 t2 ON t1.rn=t2.rn

WHERE ISNULL(t1.item_value,'')<>ISNULL(t2.item_value,'')

table2 has more values so I used RIGHT JOIN.. You can use FULL JOIN instead if you're uncertain on which of the two tables has more values..

tokyo_dude Newbie Points: 4 More actions · Answer 3

I tried the code and it worked.

Thanks a lot !

One more thing, assume that tbl1 & tbl2 are already populated then how this result can be achieved ?

tokyo_dude Newbie Points: 4 More actions · Answer 4

Hi SSC Rookie

Assume that tables are already populated, then how can i achieve the same results ?

Thanks in Advance

shield_21 Ten Centuries Points: 1033 More actions · Answer 5

Hi there,

Here's a sample for FULL JOIN.. If @table1 has more data than @table2 or vice versa, this will work.. However, this can affect performance depending on the number of your data..

DECLARE @table1 TABLE (file_no VARCHAR(10),item_no INT, item_value VARCHAR(50))

DECLARE @table2 TABLE (file_no VARCHAR(10),item_no INT, item_value VARCHAR(50))

INSERT INTO @table1

SELECT '015', 2, 'AUD' UNION ALL

SELECT '015', 2, 'CAD' UNION ALL

SELECT '015', 2, 'CHF' UNION ALL

SELECT '015', 2, 'EUR' UNION ALL

SELECT '015', 2, 'USD'

INSERT INTO @table2

SELECT '015', 2, 'EUR' UNION ALL

SELECT '015', 2, 'USD'

;WITH table1 AS

(

SELECT *,ROW_NUMBER() OVER (PARTITION BY file_no,item_no ORDER BY item_value) AS rn

FROM @table1

),

table2 AS

(

SELECT *,ROW_NUMBER() OVER (PARTITION BY file_no,item_no ORDER BY item_value) AS rn

FROM @table2

)

SELECT ISNULL(t2.file_no,t1.file_no),ISNULL(t2.item_no,t1.item_no),t1.item_value,t2.item_value

FROM table1 t1

FULL JOIN table2 t2 ON t1.rn=t2.rn

WHERE ISNULL(t1.item_value,'')<>ISNULL(t2.item_value,'')

I hope this helps.. Dou itashi mashite!:-)

tokyo_dude Newbie Points: 4 More actions · Answer 6

Hi shield_21-san

Henji(Reply) Arigatou Gozaimashita !

By using Your hint of using row_number I changed my code as follows,

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 table1)t1

FULL OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY file_no,item_no ORDER BY name_1) AS Seq,* FROM table2)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)

or (t1.name_1 is null and t2.name_1 = '' )

or (t2.name_1 is null and t1.name_1 = '' )

order by t1.file_no,t1.item_no,t1.name_1

Also instead of isnull I used COALESCE here, because outer join wont give NuLL,NuLL for the name_1 column of both tables when joining(I checked both and they give the same results).

This seems to work. Appreciate if you can vefiry this and let me know if this it good to go.

Yoroshiku Onegaishimasu

shield_21 Ten Centuries Points: 1033 More actions · Answer 7

My apologies for my late reply.. I'm quite busy at work..

Your code works! It even compares NULL against empty string and return them.. So I think this is fine.. I'm just afraid this might affect performance.. I appreciate if someone can correct me if I am wrong..

Kochira Koso..

I'm not good in japanese XD