Joining Multiple Tables - Help with Links

[lesleycampbell]

I am working on a report where I want to group by Customer, Billing Profile, and Order.
I only want the Customers to show that have active orders.
I want ALL Billing Profiles associated with the Customer to show whether they are attached to an order or not. I can not get the Billing Profiles to show that don't have orders associated with them.

I need help in linking the fields in the tables, as I think my problem is there. I have the three tables below and attached a screenshot of how I have them linked currently.
Customer Master
Billing Profile
Order Master

I want my report to show as follows:

Customer ABC
#1 Billing Profile
Order A
Order B
#2 Billing Profile
#3 Billing Profile
Order A
Order B

 

[SkipVought]

Hi,

Inner join on Customer Master & Billing Profile
Outer join on Billing Profile & Order Master

That way every Customer Master/Billing Profile for the given Customer will select.

 

[lesleycampbell]

It still is only showing the billing profiles that have orders associated with them. I have the following links:

CustomerMaster.Customer_ID INNER JOIN CustomerBillingProfile.CustomerKey

CustomerBillingProfile.BillingProfileKey LEFT OUTER JOIN OrderMaster.BillingProfileKey

I had that and it didn't work and then tried adding
CustomerBillingProfile.CustomerKey LEFT OUTER JOIN OrderMaster.Customer_ID

 

[SkipVought]

CustomerMaster cm INNER JOIN CustomerBillingProfile BP 
  ON cm.Customer_ID = BP.CustomerKey

CustomerBillingProfile bp LEFT OUTER JOIN OrderMaster om
  ON BP.BillingProfileKey = om.BillingProfileKey

 

[lesleycampbell]

Isn't that what I had with the first 2? I am linking the tables in CR, not working in SQL.

If it isn't, how do I do it in CR?

 

[SkipVought]

Don't know. Not a CR guy. But this is what I'd do in Business Objects to get the data for a report.

 

[BettyJ]

When using an outer join, you cannot have a condition on the right table. You may have to use subreports.

 

[lesleycampbell]

I did not know that! Any outer join, or if I am using a LEFT outer join? And vice versa for a RIGHT outer join?
Thank you!

 

[BettyJ]

I am not sure about RIGHT OUTER JOIN.What I wrote in the previous post definitely applies for LEFT OUTER JOIN.