×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Joining Multiple Tables - Help with Links

Joining Multiple Tables - Help with Links

Joining Multiple Tables - Help with Links

(OP)
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


RE: Joining Multiple Tables - Help with Links

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.

RE: Joining Multiple Tables - Help with Links

(OP)
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

RE: Joining Multiple Tables - Help with Links


CODE

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

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

RE: Joining Multiple Tables - Help with Links

(OP)
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?

RE: Joining Multiple Tables - Help with Links

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

RE: Joining Multiple Tables - Help with Links

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

RE: Joining Multiple Tables - Help with Links

(OP)
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!

RE: Joining Multiple Tables - Help with Links

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close