Join Table Multi Join Join Join lets play twister reltationship

[Squeaky Data]

Not sure what to call this type of relationship but I have companies that are affiliated with one another an they want to view all affiliated companies from any of the affiliated companies.

So If:

Company 1 is affiliated with Company 2

Company 1 is affiliated with Company 3

Company 3 is affiliated with Company 4

Company 4 is affiliated with Company 5

Then they should all be considered affiliated with one another and you should be able to see a list of companies 1 - 5 from any of the individual company records.

I have sorted some of this out using a join table that joins to its self multiple times but there will always be a limit to how distant the relationship can be and still connect the affiliated companies.

I have attached an example of the multiple joins. There are two layouts, one for companies and one for joins so you can see where the related companies link or do not link. Inserting more joins in the chain will increase the level of connectivity but I think there is always a limit.

Wondering if anyone has a simple solution to show all the interconnected relationships other than just putting together a number of joins?

Test_Self_Join_Many_2_Many.fp7.zip

[comment]

What exactly does "affiliation" mean? Is it possible for three companies to be affiliated with each other, so that A is affiliated with B, B with C and C with A?

If so, then I believe the problem is not how to extend the pattern, but rather how to limit it - otherwise you will end up in an exponentially growing loop.

[Squeaky Data]

And that is the problem as they have a very broad definition of "affiliation" so it could mean that they are all members of a parent company or that they have the same owner or literally any other reason that they feel that they should be considered affiliated with one another.

I was hoping there was some other approach than multiple joins but I think I will put a few in and ask that they try to identify master companies and associate the others to that rather than trying to trace through all of the possible paths.

Thanks for the reply.

What exactly does "affiliation" mean? Is it possible for three companies to be affiliated with each other, so that A is affiliated with B, B with C and C with A?

If so, then I believe the problem is not how to extend the pattern, but rather how to limit it - otherwise you will end up in an exponentially growing loop.

[comment]

Well, it's possible to do it with only one level of relationships, but it needs to be scripted: basically, you would GTRR [Match found set] in a loop, until the found set stops increasing; then you will have a found set of an entire interconnected group.

[bruceR]

Another approach. Builds lists of related keys. Not quite the full answer you're looking for but may give you ideas.

Test_Self_Join_Many_2_ManyBFR.fp7.zip

[comment]

Ahm...

FailTest.fp7.zip

Edited October 10, 2010 by Guest

[bruceR]

Ahm...

?

[bruceR]

Gave me ideas anyway. How about this.

Test_Self_Join_Many_2_ManyBFR.fp7.zip

[comment]

I am not sure by what logic it's supposed to work, but in any case it doesn't.

FailAgain.zip

[bruceR]

As I mentioned VERY clearly, this is to give you ideas. Or the original questioner ideas.

It does have a limited hierarchy depth for single continuous hierarchies. (1:2; 2:3; 3:4; 4:5 etc). However even a single cross record results in an all match. If you want deeper hierarchies you can extend the calculation technique if you choose to go this route.

It is an available technique with a simple graph and no scripting. There are other techniques and the person posing the question will have to figure out what methods and what limitations are acceptable.

Surely you can't be serious about having difficulty figuring this out.

FailNot.fp7.zip

Edited October 10, 2010 by Guest

[comment]

Surely you can't be serious about having difficulty figuring this out.

Why not? You posted a file that shows a method. The method is not explained, and it doesn't work in all circumstances. No limitations are stated. Am I supposed to spend a day trying to figure out what your idea was, when I already know it doesn't work?

If you want deeper hierarchies you can extend the calculation technique if you choose to go this route.

I am not at all convinced that is the case. I'd suggest you do some reading on graph theory and perhaps the traveling salesman problem, which although more difficult than the current problem, gives a good idea of what is involved here.

[bruceR]

Why not? You posted a file that shows a method. The method is not explained, and it doesn't work in all circumstances. No limitations are stated. Am I supposed to spend a day trying to figure out what your idea was, when I already know it doesn't work?

Untrue. As I have now repeated several times.

Yes, as a matter of fact you ARE supposed to spend your time figuring it out. Again - you can't be serious. It won't take you a day. Figuring out what you understand, what you don't understand, and what you understand but want to waste your time arguing with me about just is not where I'm willing to spend my time.

If you were serious you'd describe the problem. WHAT doesn't work? You clearly put effort into creating a very particular hierarchy that would illustrate the limits of the demonstration of the technique, so your claim that you can't figure this out is disingenuous.

You're welcome to post compliments:

http://fmforums.com/forum/showpost.php?post/367793/

[bruceR]

I am not at all convinced that is the case. I'd suggest you do some reading on graph theory and perhaps the traveling salesman problem, which although more difficult than the current problem, gives a good idea of what is involved here.

Then put your effort into establishing a proof of your statement that meets your expectations.

[comment]

Are you calling me a liar?

[bruceR]

I am not at all convinced that is the case. I'd suggest you do some reading on graph theory and perhaps the traveling salesman problem, which although more difficult than the current problem, gives a good idea of what is involved here.

Calc extended, hierarchy depth extended.

QED

FailNot.fp7.zip

[bruceR]

I am not at all convinced that is the case. I'd suggest you do some reading on graph theory and perhaps the traveling salesman problem, which although more difficult than the current problem, gives a good idea of what is involved here.

Bees do it better: solving the traveling salesman problem:

http://www.qmul.ac.uk/media/news/items/se/38864.html

[Vaughan]

So we need to use b-trees then?