Ambiguous Joins

[cogcurious]

My understanding of ambiguous joins is that one exists if we have multiple joins from different query items in one query subject to the same query item in another query subject. Is that correct? And why is it a problem?

Would multiple joins from different query items in a query subject to different query items in another query subject qualify as well as a scneario of ambiguous join

[blom0344]

Ambiguous would first and foremost be about the issue in which the joins have to be applied. It is not uncommon to join 2 tables over multiple columns (2nd part of your  question)

Your first example would probably never yield any data, except when you use theta (non-equal ) joins

[cogcurious]

Apologies for asking again. Consider the below

Table 1          Table 2
Key 1             Key4
Key 2           

If i have a join from Key 1 to Key 4 and a join from Key 2 to Key 4....Does that mean its an ambiguous join  just because 2 fields from Table 1 are joining to 1 field of table 2

[cognostechie]

Think about it -

If Key1 and Key 2 are both joined to Key 4, that means Key1 and Key2 have the same data and are always going to have the same data (if the operator is the same). So what's the need to having both the joins ?

Ambigous not only refers to this - It means more than one way to reach from Table 1 to Table2. You could prtentially reach Table 2 from Table 1 via Table 3 too.

[MFGF]

Apologies for asking again. Consider the below

Table 1          Table 2
Key 1             Key4
Key 2           

If i have a join from Key 1 to Key 4 and a join from Key 2 to Key 4....Does that mean its an ambiguous join  just because 2 fields from Table 1 are joining to 1 field of table 2

Following on from what the other guys have said...

If Key 1 and Key 2 contain different values in each row of data (eg they are two different dates, for example) then setting up two relationships from Table 1 to Table 2 (Key 1 <-> Key 4 and Key 2 <-> Key 4) would definitely class as an example of ambiguous relationships.  The crux of the matter is that only one relationship can be used to link the two tables (query subjects) at any single point in time, and if you provide a choice of two relationships, which would your query use? The answer is the first alphabetical relationship (based on the name you defined for the two relationships). Sometimes you will be lucky and this will be the correct one to use, but other times it will not.

The best practice technique for modelling this would have you creating two versions of Table 2 as different query subjects in your model, and splitting your multiple relationships apart in this way - one to each version of Table 2.

Regards,

MF.

[tjohnson3050]

If you are refering to an ambiguous join path, that is when you have two query subject that are not directly related, but are related to each other multiple ways through other query subjects. 

In framework manager, these can be identified by running the model advisor.  It is a modeling best practice to identify these and address them by creating role playing dimensions.  These can be separate query subjects, or alias shortcuts.