Home » SQL & PL/SQL » SQL & PL/SQL » Comparing two tables
Comparing two tables [message #188348] Fri, 18 August 2006 02:53 Go to next message
psantha
Messages: 5
Registered: August 2006
Junior Member
Hi... I need to compare these two tables as described below.

NT
id b_name V1 V2 V3 V4
23424d D1 15 AX2 456 YPBG
23424d D2 15 AX2 456 YPBG
23434d D1 15 AX2 456 YPBG
23444d D3 15 AX2 456 YPBG

OT
id b_name V1 V2 V3 V4
23424d D1 15 AX2 456 YPBG
23424d D2 15 AX3 456 YPBG
23434d D1 15 AX2 456 YPSG
23444d D3 15 AX2 456 YPBG


Description:
Both tables have the same set of rows identified by the primary key(id and b_name).
For a matching row there may be some columns that has different values.
I want to pull out the columns that are different for a matching row.
E.g for Matching row : 1st row of NT and OT.
E.g for Matching row with different values : 2nd row of NT and OT. Column V2 differes.

Result i need would look like
23424d D2 AX3
23434d D1 YPSG

I could get only as far as pulling out matching rows that has different values.
I could not identify the columns that were different.

Can somebody help me on this.
Re: Comparing two tables [message #188351 is a reply to message #188348] Fri, 18 August 2006 03:10 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And how do you expect the result when more than one column has a different value?

MHE
Re: Comparing two tables [message #188364 is a reply to message #188348] Fri, 18 August 2006 04:06 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
I guess this would be 1 way.
(I altered a few values to create more mismatches)
SQL> select * from nt;

ID         B_NAME     V1 V2          V3 V4
---------- ---------- -- --- ---------- ----
23424d     D1         15 ax2        456 ypbg
23424d     D2         15 ax2        456 ypbg
23424d     D1         15 ax2        456 ypbg
23424d     D3         15 ax2        455 XXX

SQL> select * from ot;

ID         B_NAME     V1 V2          V3 V4
---------- ---------- -- --- ---------- ----
23424d     D1         15 ax2        456 ypbg
23424d     D2         15 ax3        456 ypbg
23424d     D1         15 ax2        456 ypbg
23424d     D3         15 ax2        456 ypbg

SQL> (select id, b_name, nt.v1 v1, null v2, null v3, null v4
  2  from nt
  3  join ot
  4  using (id, b_name)
  5  where nt.v1 <> ot.v1
  6  union all
  7  select id, b_name, ot.v1, null, null, null
  8  from nt
  9  join ot
 10  using (id, b_name)
 11  where nt.v1 <> ot.v1)
 12  union
 13  (select id, b_name, null, nt.v2, null, null
 14  from nt
 15  join ot
 16  using (id, b_name)
 17  where nt.v2 <> ot.v2
 18  union all
 19  select id, b_name, null, ot.v2, null, null
 20  from nt
 21  join ot
 22  using (id, b_name)
 23  where nt.v2 <> ot.v2)
 24  union all
 25  (select id, b_name, null, null, nt.v3, null
 26  from nt
 27  join ot
 28  using (id, b_name)
 29  where nt.v3 <> ot.v3
 30  union all
 31  select id, b_name, null, null, ot.v3, null
 32  from nt
 33  join ot
 34  using (id, b_name)
 35  where nt.v3 <> ot.v3)
 36  union all
 37  (select id, b_name, null, null, null, nt.v4
 38  from nt
 39  join ot
 40  using (id, b_name)
 41  where nt.v4 <> ot.v4
 42  union all
 43  select id, b_name, null, null, null, ot.v4
 44  from nt
 45  join ot
 46  using (id, b_name)
 47  where nt.v4 <> ot.v4)
 48
SQL> /

ID         B_NAME     V1 V2          V3 V4
---------- ---------- -- --- ---------- ----
23424d     D2            ax2
23424d     D2            ax3
23424d     D3                       455
23424d     D3                       456
23424d     D3                           XXX
23424d     D3                           ypbg

6 rows selected.


Re: Comparing two tables [message #188366 is a reply to message #188351] Fri, 18 August 2006 04:10 Go to previous messageGo to next message
psantha
Messages: 5
Registered: August 2006
Junior Member
This is a big problem that i am facing.

Ideally i would like it in a table with each row having the primary key followed by all the differences. But donot have much idea how that can be done with sql queries.
Can you tell me if this can be done or can you suggest how we can get the output.

Only requirement is i need all the values that are different for a particular row in whatever format possible. The output is for manual inspection only.
Re: Comparing two tables [message #188367 is a reply to message #188348] Fri, 18 August 2006 04:11 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
This could be something to start with:

SELECT ID, B_NAME, V1, V2, V3, V4 FROM NT
MINUS
SELECT ID, B_NAME, V1, V2, V3, V4 FROM OT
UNION ALL
SELECT ID, B_NAME, V1, V2, V3, V4 FROM OT
MINUS
SELECT ID, B_NAME, V1, V2, V3, V4 FROM NT
Re: Comparing two tables [message #188379 is a reply to message #188364] Fri, 18 August 2006 04:58 Go to previous messageGo to next message
psantha
Messages: 5
Registered: August 2006
Junior Member
Solution proposed by JSI2001 is really useful.
But the real table on which i have to implement this solution has some 75 columns. This solution may prove quite difficult to implement.

Is there any other way for doing this considering a table with 75 columns and 10,000 records.
Re: Comparing two tables [message #188394 is a reply to message #188379] Fri, 18 August 2006 06:34 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The same principle applies. I'd let SQL generate the statement for me (hint: user_tab_columns)

MHE

[Updated on: Fri, 18 August 2006 06:34]

Report message to a moderator

Previous Topic: How can I fin '_'-mark in text-string
Next Topic: Sorting weird date format
Goto Forum:
  


Current Time: Fri Dec 09 23:32:25 CST 2016

Total time taken to generate the page: 0.21561 seconds