How to provide joins between any 2 tables in FM

[yogeswari]

Hi All,

Can anyone let me know how to give join between any 2 tables in FM.  The joins to my knowledge are:
1)left outer join
2)right outer join
3)full outer join
4)inner join

I know we can establish using Relationship and then providing cardinalities.  But i am not aware of defining the cardinalities for the above joins.  I mean for each joins, there would be predefined cardinalities.  If so, how to define them for all the kinds of joins.
I am attaching the screenshot for your references.
I would consider your inputs as Great and much appreciated.

Thanks.

[MFGF]

Hi All,

Can anyone let me know how to give join between any 2 tables in FM.  The joins to my knowledge are:
1)left outer join
2)right outer join
3)full outer join
4)inner join

I know we can establish using Relationship and then providing cardinalities.  But i am not aware of defining the cardinalities for the above joins.  I mean for each joins, there would be predefined cardinalities.  If so, how to define them for all the kinds of joins.
I am attaching the screenshot for your references.
I would consider your inputs as Great and much appreciated.

Thanks.

Hi,

Outer join means that the cardinality is optional (ie starts with a 0). Inner join means the cardinality is required (ie starts with a 1)

A left outer join is usually

1..x <--> 0..x

A right outer join is usually

0..x <--> 1..x

A full outer join is usually

0..x <--> 0..x

An inner join is usually

1..x <--> 1..x

Cheers!

MF.

[yogeswari]

Thank you so much for your inputs. 

I have one concern: What about the operators (=,>,<,>=,<=)?   Is these operators are related to joins (left outer join, right outer join, inner join, full outer join) anywhere?

I understand from your inputs is all joins will have "=" operator.  Is there any specific operator related for any particular joins?  If so or not, please provide your thoughts.

Thanks again and in advance.
Please suggest.


thanks.

[MFGF]

Thank you so much for your inputs. 

I have one concern: What about the operators (=,>,<,>=,<=)?   Is these operators are related to joins (left outer join, right outer join, inner join, full outer join) anywhere?

I understand from your inputs is all joins will have "=" operator.  Is there any specific operator related for any particular joins?  If so or not, please provide your thoughts.

Thanks again and in advance.
Please suggest.


thanks.

A "normal" relationship is an equi-join (ie value = value). You can easily change it to a non-equi join by using the operator dropdown in the middle of the relationship dialog. If you look closely, you will also see an ellipsis on the right-hand side. This gives you access to the expression used in the join, and you can modify the expression to (for example) extract parts of keys from one table to join to another, if required.

Cheers!

MF.

[yogeswari]

Thank you very much for your inputs.  I am unable to get you back soon.My apologies.

Mostly we would use the below joins
1)Inner join
2)Outer join (Left, right, full)
3)cartesian join.

For Inner Join:
[Application Layer].[Data].[CD]=[Application Layer].[Column].[CD]
AND
[Application Layer].[Data].[COLUMN_NO]=[Application Layer].[Column].[COLUMN_NO]

Is the above joins is correct for inner join?  If wrong, correct me please.

Can you plz show me an example of expressions for other joins above.


Thanks.

[yogeswari]

Ohhh...................I missed to add in my previous post

I would like to know the expression for other joins in point of perspectie from FRAMEWORK MANAGER and not in oracle.

I am aware of expression in oracle but in FRAMEWORK MANAGER i am not aware of those and i am starter to FM.

Please help.

[MFGF]

Thank you very much for your inputs.  I am unable to get you back soon.My apologies.

Mostly we would use the below joins
1)Inner join
2)Outer join (Left, right, full)
3)cartesian join.

For Inner Join:
[Application Layer].[Data].[CD]=[Application Layer].[Column].[CD]
AND
[Application Layer].[Data].[COLUMN_NO]=[Application Layer].[Column].[COLUMN_NO]

Is the above joins is correct for inner join?  If wrong, correct me please.

Can you plz show me an example of expressions for other joins above.


Thanks.

Hi,

As detailed above, an inner join simply means the cardinalities dictate a row must exist on both sides (ie the cardinality at each end of the relationship starts with a 1). It doesn't matter whether you use equi-joins (=) or non-equi-joins (eg <) or whether the relationship is based on one item or multiple items. What you wrote above doesn't detail whether the join is inner or outer - all it shows is that it is an equi-join based on two items.

If you look again at the post I wrote earlier in the thread, you can see that you specify inner or outer joins using the cardinality. I included examples so you can see what the available options are.

The one thing you didn't mention earlier was a cartesian join. This is an odd one, because it means, in simple terms, there is no specified join, and you allow every row from the first table to join to every row from the second. In Framework Manager it's referred to as a Cross Product, and it's disallowed by default. You can change this by going to the Governors dialog and allowing it for the model. Then, where there is no specified join in the model, a cartesian product (cross product) will be performed at runtime. It's not a good thing, though - try to avoid these if at all possible.

MF.