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.
| Author |
Topic |
|
Cyclonik
Posting Yak Master
114 Posts |
 Posted - 2006-01-24 : 15:11:30
|
| I have a dataset that looks like this:2 Columns ID1, ID2 where the pair means equality based on some criteria irrelevant to this.In the data I will have a lot of pairs like thisA,B and B,C and B,D-> so A=B and B=C and B=D and therefore A=C, A=D, C=D etc.. Edit: There is also plenty of things like A,B and A,C and B,C etcI need to traverse this table and group ( assign a common number or group id) for instance) all those transitively equal single values. essentially I am matching customer data which I have matched pairs based on different criteria. I am having some difficulty coming up with this. Any insight would be much appreciated.Christian-=:SpasmatiK:=- |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-01-24 : 15:45:47
|
quote:
Originally posted by CyclonikI am having some difficulty coming up with this.
You are having difficulty explaining this. Try again, because I can't tell what your actual data is, or exactly what you want to do. Post some sample data in tabular format, or quoted, or something... |
 |
|
|
Cyclonik
Posting Yak Master
114 Posts |
Posted - 2006-01-24 : 15:53:38
|
| My apologies.Data looks like this:Id1 ID21 22 43 45 66 7each pair are deemed equal by a previous process. As you can see 1=2 and 2=4 and 3=4 therefore 1=2=3=4( as well 5=6 and 6=7 therefore 5=7 -> 5=6=7)i need to be able to group all the ID's that are equal together. I am matching customers coming in from many sources. I need to group them so I can merge the data and create an accurate customer list.Is that more clear? The table is built by 4 queries that look like the one on this thread:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60777[/url] ***Support Info****This is a little tricky. A customer can have any of 3 contact types (Postal, email, phone). WE've decided that 4 combinations of data = a matched customer(firstname+lastname+street address), (firstname+lastname+postalcode), (firstname+lastname+email), (firstname+lastname+PhoneNumber)I am matching based on the those criteria in that order and exluding any results that already exist from any of the previous queries |
|
|
|
Cyclonik
Posting Yak Master
114 Posts |
Posted - 2006-01-24 : 16:44:51
|
| Looking over my original queries it looks like I wil get all the combinations of people with matching information:i.e. if 1,2,3,4,5 all match (firstname+lastname+address) i will have the following pairs in the table1,21,31,41,52,32,42,5...Thanks to blindman I no longer get all perms. |
|
|
|
Cyclonik
Posting Yak Master
114 Posts |
Posted - 2006-01-25 : 10:04:54
|
| Is the only way to accomplish this to write a recursive sproc? |
|
|
|
Norwich
Posting Yak Master
158 Posts |
Posted - 2006-01-25 : 10:34:27
|
| Please post the expected Result Set Please - I work better if I know what you are expectingThe revolution won't be televised! |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-01-25 : 10:35:34
|
| You will need some sort of loop. Whether it is a WHILE loop, a cursor, or recursion, is up to you. I would steer clear of recursion in SQL. It tends to be inefficent, and has a maximum next level of 32 plys. My personal preference would be a UDF that accumulates related records using a WHILE loop. |
|
|
|
Cyclonik
Posting Yak Master
114 Posts |
Posted - 2006-01-25 : 10:39:22
|
| I don't have an exact result set I am looking for. I would prefer something that assigned an ID to all equal ID's. For instance:SourceA,BA,CB,CD,EE,FPossible Result set.A,1B,1C,1D,2E,2Make sense? |
|
|
|
|
|
|
|
|
Recursive linking