Home » SQL & PL/SQL » SQL & PL/SQL » Difference in columns
Difference in columns [message #21666] Wed, 21 August 2002 19:17 Go to next message
Marty
Messages: 2
Registered: August 2002
Junior Member
Hi

I have two tables T1 (with columns account, descr, date) and T2 (with columns account, descr, date). Both have similar column strutcures. An example of data rows in the two tables is as following.

T1 - 123, acct-1, 08-22-02( in account, descr, date)
T2 - 123, acct-2, 08-22-02( in account, descr, date)

The data in all columns are similar except the descr.

I need to write a query to get only the colums that have different data - T1.descr and T2.descr. How do I do that?

I can use UNION but that will give me 2 rows. But I need just those columns that are different.

Thanks
Marty

Report message to a moderator

Re: Difference in columns [message #21669 is a reply to message #21666] Wed, 21 August 2002 20:30 Go to previous messageGo to next message
Mark
Messages: 284
Registered: July 1998
Senior Member
If you already know what columns then:

select t1.description T1,t2.description T1 from t1,t2;
OR:
select t1.description T1,t2.description T2 from t1,t2
where t1.description
NOT IN
(select t2.description from t2,t1
where t1.acct = t2.acct);

You may want to compare columns using an id column.

Report message to a moderator

Re: Difference in columns [message #21674 is a reply to message #21666] Thu, 22 August 2002 03:07 Go to previous messageGo to next message
vipin
Messages: 40
Registered: May 2002
Member
select v.id, v.name from u, v where v.id = u.id and decode(v.name, u.name, null, v.name) is not null;

Report message to a moderator

Re: Difference in columns [message #21678 is a reply to message #21666] Thu, 22 August 2002 06:34 Go to previous messageGo to next message
Martin
Messages: 83
Registered: February 2000
Member
Thank you all for the reply. I really appreciate it.

Is there anyway I can make the column comparison dynamic? In otherwords any of the columns (not just descr) can be different. In that can I say

select "columns that are different" T2 from t1,t2
where t1.acct
NOT IN
(select * from t2,t1
where t1.* <> t2.*);

How do I make this happen in sql?

Report message to a moderator

Re: Difference in columns [message #21685 is a reply to message #21666] Thu, 22 August 2002 10:29 Go to previous message
Mark
Messages: 284
Registered: July 1998
Senior Member
Try this:
select distinct t1.acct,t1.description,t1.date_1,t2.acct,t2.description,t2.date_1 from t1,t2
where t1.acct <> t2.acct
and t1.description <> t2.description
and t1.date_1 <> t2.date_1;

Report message to a moderator

Previous Topic: pl/sql compiler options
Next Topic: bind variable GENCR not declared
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 10 09:56:52 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.