OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » matching problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

matching problem [message #372550]

Wed, 21 February 2001 16:09

kgentile
Messages: 2
Registered: February 2001

Junior Member

I'm trying to match up records in a table if they have the same address or they have the same email. I want to assign a "group id" to them if they are the same person.

FNAME LNAME ADDR EMAIL
------ ---------- ------- ---------------
Jon Smith Main jon.smith@wh.com
J Smith Main j.smith@wh.com
John Smith Main john.smith@wh.com
Jon Smith Elm jon.smith@wh.com
J Smith Elm jon@wh.com
Jay Sweeney Oak js@yahoo.com
J Sutter Line jsutter@abc.com
Alfred Newman abc al.newmand@mad.com
A Newman abc al.e.nemand@mad.com
Alfred Newman xyz al.e.newman@mad.com
Al Newman xyz alnewmanr@mad.com
A Newman xyz anewman@mad.com

I'm looking to match up records who have the same address or records who have the same email address,
and create a new column which will assign a number to all the records that have matches.
For example, above the first 5 records above would all match, cause if they have the same address or email,
they're the same person. So, my result table would look like

FNAME LNAME ADDR EMAIL MATCHID
----- ------ -------- --------- ---------
Jon Smith Main jon.smith@wh.com 1
J Smith Main j.smith@wh.com 1
John Smith Main john.smith@wh.com 1
Jon Smith Elm jon.smith@wh.com 1
(cause matches on email jon.smith@wh.com)
J Smith Elm jon@wh.com 1
(cause matches address Elm)
Jay Small Oak js@yahoo.com 2
J Sutter Line jsutter@abc.com 3
Alf Newman abc al.newmand@mad.com 4
A Newman abc al.e.newman@mad.com 4
Alf Newman xyz al.e.newman@mad.com 4
(cause matches email)
Al Newman xyz alnewmanr@mad.com 4
A Newman xyz anewman@mad.com 4

Is there a way to do this with sql or pl/sql. I can get the matches on addr or emails, but if I have two groups,
then I can't get them to be all the same id.

Thanks for any ideas.

Report message to a moderator

Re: matching problem [message #372557 is a reply to message #372550]

Thu, 22 February 2001 04:04

J.S HAN
Messages: 12
Registered: November 2000

Junior Member

Hi
Try to a SQL

select y.FNAME, y.LNAME, y.ADDR, y.EMAIL, x.group_id
from (select rownum group_id, email
from (select EMAIL from tableemail group by email)) x,
tableemail y
where x.email = y.email;

If tableemail table is very very big,
it's no good.

Having index on email column,
Try to tuning by other sql.

Have nice day.

Report message to a moderator

Re: matching problem [message #372563 is a reply to message #372550]

Thu, 22 February 2001 08:59

kgentile
Messages: 2
Registered: February 2001

Junior Member

Well, that will give the match on the email. What I have to do is match on the email AND match on the address. I can get a "group" id for matches on the addresses, and a "group" id for matches on the email. But what I want to assign an id for matches on email or address.

thanks

Report message to a moderator

Previous Topic:	sql
Next Topic:	Indexes

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 06:43:42 CDT 2024