cmp likely data from two diff tables

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

cmp likely data from two diff tables

Author

Topic

Anusha7
Starting Member

5 Posts

Posted - 2011-10-20 : 09:47:10

hello people,

i have two tables with 2 columns in each.
table 1 : col1 and col 2
table 2 : col 3 and col4

the col2 and col 4 have identical data. for example, cal2 says "most recently observed" and col 4 says "most recently observed in past".

and my tables have huge data....

how can i compare them and retrieve only those rows that have identical data...kindly help...

thanks in advance...

Software Learning is an ocean...there is never an end to it

Anusha7
Starting Member

5 Posts

Posted - 2011-10-20 : 09:54:17

i am in real need of a solution...Please suggest of any possible way...:)

Software Learning is an ocean...there is never an end to it

DBAPBFL
Starting Member

11 Posts

Posted - 2011-10-20 : 10:15:04

try the checksum() function...

create table #tmp1 (col1 varchar(10),col2 varchar(100))
create table #tmp2 (col3 varchar(10),col4 varchar(100))

insert #tmp1 values ('1','most recently observed')
insert #tmp1 values ('2','most recently observed in past')
insert #tmp2 values ('1','most recently observed in past')

select * from #tmp1
select * from #tmp2

select * from #tmp1 a inner join #tmp2 b on checksum(a.col2)=checksum(b.col4)

Anusha7
Starting Member

5 Posts

Posted - 2011-10-20 : 10:27:35

thanks a ton...

my table structure looks like this...

CREATE TABLE [dbo].[global](
[g_prob] [varchar](200)
[g_inter] [varchar](500)
) ON [PRIMARY]

and

CREATE TABLE [dbo].[local](
[l_prob] [varchar](200)
[l_inter] [varchar](500)
) ON [PRIMARY]

and here is a replica of the data in the two tables,

global

g_Prob --- g_inter
___________________

eat --- eats regular
drinking --- is addicted

l_prob --- l_inter
_____________________

eat --- eats regularly
drinking --- is addicted to drink

i guess now you would get an idea...my problem is the duplicates in both the tables under columns g_inter and l_inter are differing just by a spelling or an extra word. identifying such records among thousands of records is making me sickkkkkkk

...

plz suggest any possible solution...

Software Learning is an ocean...there is never an end to it

Anusha7
Starting Member

5 Posts

Posted - 2011-10-20 : 10:32:20

we also need to consider the col g_prob and l_prob ...as the same value under g_inter can be many times repleted. say for example 'is addicted' can be for smoking under g_prob. i.e

g_Prob --- g_inter
___________________

eat --- eats regular
drinking --- is addicted
smoking --- is addicted

please let me know, if i am unclear...and thanks for all the help in advance...this means a lot to me...

Software Learning is an ocean...there is never an end to it

DBAPBFL
Starting Member

11 Posts

Posted - 2011-10-20 : 10:43:27

I think you are looking for a "fuzzy match". check out this thread...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107531

Anusha7
Starting Member

5 Posts

Posted - 2011-10-21 : 00:24:37

thanks a ton...it really worked.....

Software Learning is an ocean...there is never an end to it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources