Xmas Transitivity Puzzle

Question

Post reply

Xmas Transitivity Puzzle

tpbury

Grasshopper

Points: 20
More actions
December 24, 2013 at 12:14 am

#396025

Hi,
I have a problem which I cannot solve, and I don't even know if transitivity is the correct term.
Basically
a=b
b=c
therefore
a=c
create table mkey1
(T_ID int, A_key int, P_Key int, E_key int, G_Number int)
;
insert into mkey1 values (1,1,2,3,null) ;
insert into mkey1 values (2,3,3,4,null) ;
insert into mkey1 values (3,3,4,3,null) ;
T_ID 1 has no common A_Key, P_Key or E_Key with T_ID 2 and therefore doesn't match.
T_ID 3 matches T_ID 1 on E_Key and T_ID 2 on P_Key.
I want to assign all rows the same G_number. I don't have to use this data structure, so any suggestions gratefully accepted. In the real world, these are match keys from Address, Email and Phone matching processes and we know want to create the customer view of all attributes.
Thanks and happy Xmas!
T

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

does joining the table against it self help you visualize the issue?

this lets us see how some of the items are related to each other:

--join the table against itself looking for common fields (address/phone/email)

select

T1.*,

T2.*

from mkey1 T1

INNER JOIN mkey1 T2

ON ( T1.A_key = T2.A_key

OR T1.P_key = T2.P_key

OR T1.E_key = T2.E_key)

WHERE T1.T_ID <> T2.T_ID

not sure what you want to update G_Number to (A group number?)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tpbury Grasshopper Points: 20 More actions · Answer 2

Thanks for that Lowell, I'll try it out on Friday.

T

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

What if there are overlaps in your groups?

How will you maintain the group number in the event your underlying data changes?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

tpbury Grasshopper Points: 20 More actions · Answer 4

Thanks for your reply.

The whole overlap scenario is what has caused me to try and work with the simplest test case I can make, and then add complexity.

Fortunately, this is a one off load, but I understand incoming changes on say an email address could cause previously unlinked individual rows to be joined and it will be necessary to re-key all newly grouped rows.

I declare myself guily of trying to solve the initial problem and worry about key maintenance later!