Matching records according to 3 column

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

SQL Server 2005 Forums

Transact-SQL (2005)

Matching records according to 3 column

Author

Topic

mtoru
Starting Member

8 Posts

Posted - 2010-05-27 : 05:08:40

Hi all;

I really confused with this problem which I have tried to solve for 3 days. Relly thanks for any help

The numbers in the email,Mobile and WebStie columns are IDs which is generated by another proram. The IDs shows the matching records by marked them with same ID.

Person Email Mobile WebSite
Person 1 100001 200001 300001
Person 2 100002 200002 300002
Person 3 100003 200003 300003
Person 4 100001 200004 300004
Person 5 100004 200005 300005
Person 6 100005 200004 300006
Person 7 100006 200004 300007
Person 8 100007 200006 300002
Person 9 100001 200007 300009
Person 10 100002 200008 300010

PERSON 1, PERSON 4 and PERSON 9 are matching PERSONs acording to Column 1
PERSON 4, PERSON 6 and PERSON 7 are matching PERSONs according to Column 2
PERSON 2 and PERSON 8 are matching PERSONs according to Column 3

With these informations;
PERSON 1, PERSON 4, PERSON 6, PERSON 7 and PERSON 9 are the same person (matching Records.)
PERSON 2, PERSON 8 ans PERSON 10 are the same person (matching records)

There are 60.000 rows in the table and I need to write a tsql code to handle this situation. With code; another column was created and all matching records according to all three column with marked another ID like 4000001,4000002.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 05:12:48

I need to write a tsql code to handle this situation
Sorry but it is not clear to me.
What is your question?
What is your needed result in relation to the example data?

No, you're never too old to Yak'n'Roll if you're too young to die.

mtoru
Starting Member

8 Posts

Posted - 2010-05-27 : 05:18:26

Thanks for quick reply Fred.

Here is the final table;

Email Mobile WebSite MATCED
Person 1 100001 200001 300001 400001
Person 2 100002 200002 300002 400002
Person 3 100003 200003 300003 400003
Person 4 100001 200004 300004 400001
Person 5 100004 200005 300005 400004
Person 6 100005 200004 300006 400001
Person 7 100006 200004 300007 400001
Person 8 100007 200006 300002 400002
Person 9 100001 200007 300009 400001
Person 10 100002 200008 300010 400002

New MATCED column will be created and all Persons matched by email,mobile and website column classified with an generated ID.

mtoru
Starting Member

8 Posts

Posted - 2010-05-27 : 06:00:42

It will be easier to describe it with a picture;

Here is the explanation again;
PERSON 1, PERSON 4 and PERSON 9 are matching PERSONs acording to Column 1
PERSON 4, PERSON 6 and PERSON 7 are matching PERSONs according to Column 2
PERSON 2 and PERSON 8 are matching PERSONs according to Column 3

Final matches with these informations;
PERSON 1, PERSON 4, PERSON 6, PERSON 7 and PERSON 9 are the same person (matching Records.)
PERSON 2, PERSON 8 ans PERSON 10 are the same person (matching records)

MATCHED column not exist at the moment. With a tsql code; the column will be created and filled with an ID. This ID shows the final matches.

More;

As you see in the picture PERSON 1 and PERSON 6 doesnt have any matching info in all three columns. But they are matching because they are both matching with PERSON 4 and PERSON 1 and PERSON 6 are the same person.

Sorry for long post and bad English. Thank you so much again and hope someone give me clue.

mtoru
Starting Member

8 Posts

Posted - 2010-05-27 : 17:01:37

Still no answer. Any suggestion please?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources