Record selection in Crystal Report

[lana123]

Hi,people,
I work with CR 9.2 and Oracle db and I have a big problem with the development of a new report.
I have 3 tables:
Invoices,Invoice_Details and Invoice_Challenges.
Relation Invoices and Invoice_Details is one-to-many.Some challenges(Invoice_Challenges) are connected with the whole Invoice(Invoices,because it's challenge for whole invoice);
some challenges are connected with Invoice_Details(if challenge for some detail).Some of Details don't have any challenges.
Here what I have to write and I cannot show now in Report:
Select info from 3 tables:
show invoices with only Invoice_Details which have challenges and show these challenges for each detail(if detail doesn't have challenge it does't show up);
if challenge item is connected with whole invoice,I have show this challenge without any details(in group "whole invoice challenge").
Invoices.Invoices_pk = Invoice_Details.Invoices_fk and Invoice_Details.Invoice_Detail_pk = Invoice_Challenges.child_fk and Invoice_Challenges.Invoices_fk = Invoices.Invoices_pk
OR
Invoices.Invoices_pk = Invoice_Details.Invoices_fk and Invoice_Challenges.Invoices_fk = Invoices.Invoices_pk .
I'll appreciate any answer.Thanks,

Lana

 

[zippo3]

Lana
I think I would do this with a series of views.
In v1 I would join the tables on an equal join that would eliminate invoices that do not have a challenge.
In view 2 I would use a left join and show all the details per invoice