Outer Join In RN

[rlcaputo]

I am using this join which works but does not bring back the non-matched rows, of which I need.

[Booked].[TIER 1 ORDER]=[Lead Time].[TIER 1 ORDER]

This line is in the filter section. How or what is syntax to do an Outer Join?

bc

[bdybldr]

Assuming Booked contains the data you want to return, you can try using this...

Lead Time. [TIER 1 ORDER] IS NULL
OR
Booked.[TIER 1 ORDER] = Lead Time.[TIER 1 ORDER]

The easiest way to accomplish this is to set the cardinality in the model.Ã,  Click on the relationship and set the side of the outer join to 0..n or 0..1.

[rlcaputo]

More Info

I would like the result of this to be the combination ( = join) and all rows from both sides that do not match. Tier1.Number is never NULL.

[bdybldr]

Can you post an example of want you're trying to accomplish.  Layout the tables and some sample data and let us know the result that you're trying to achieve.  I'm not sure I understand your requirements.

Are you not able to define the joins in FM?

[rlcaputo]

Hold on that gives me a thought, Booked is always there. Lead time is limiting the result by not having a ship date. (even though booked). I am graphing Booked vs Lead Time and need these queries joined to put on one graph. Ã, I will try the above with modification.

[bdybldr]

OK...good.  Please keep us posted.  Thanks.

[GoWestGw]

Just a quick note, I think that whenever a filter expression includes a join statement is in effect an inner join.

Similarly if you have a query with an outer join defined between them, but filter on the right-hand sided table the join is in effect an inner join. e.g.

Select Sales.*, OrderHeader.*
from Sales Left Outer Join OrderHeader
Where OrderHeader.OrderType ='Internet'

[bdybldr]

So couldn't you use something like...

Ship Date IS NULL OR Booked.[Tier 1 Order] = Lead Time.[Tier 1 Order]

Assuming that you want to return everything for Booked whether or not it has an associted Lead Time record.

[MrO]

don't forget to enable it in the properties!