linking 1 to many relationship and Join Types

[Mack2]

Two of the tables in my report is customers and payment types. I want to return customers if they have a payment type assigned to them or not. I would think that the payment table is the primary table (one payment can have many customers). So to get the all of the customers, I would think you need a right outer join (all records from the foreign table). But it only worked with a left outer join. Crystal thinks that the customers table is the primary table. This makes no sense. One customer can not have many payment types. Please help, this does not compute. Hope this makes sense. THANKS!!!!!

 

[Turkbear]

Hi,
Crystal does not think....

The order of the tables is up to you..use the Visual Linking wizard and order the tables from left( Payment) to right(Customer)..create a Right Outer Join from Payment to Customer.

Should do what you need....

 

[Mack2]

Thanks for your reply. That is what I did. Which makes sense to me. But it would not return any records with a right outer join. I switched to a left join, and it worked. But that does not make sense, that is should work. I believe the payments table is the primary, so to get all of the customers, I would think (like you said) we need a right join. That is the confusing part

 

[Turkbear]

Hi,
Are you sure that Payments contains a valid link to customer and returns multiple customers for each payment?

Can you post the metadata for the 2 tables and show what the linking field is..

It would also help to have basic info:
Crystal Version
Database and Version
Connection Type

Thanks,

 

[lbass]

I think you should use a left join FROM customer to payment, with no selection criteria on the payment table in order to show all customers.

-LB

 

[kmcginn]

I agree with lbass. I would make Customer the primary table and link Payment table to that with a left outer join. It doesn't matter that multiple customers can have the same payment type. Each customer still only has one payment type. The other tables you are using in your report probably all have a link to customer, correct? Try it and see if you get the desired results.