Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

One to Many

Status
Not open for further replies.

CPK6104

IS-IT--Management
Dec 19, 2007
57
US
I may be trying something that can't be done : )

Using: CR2008, Oracle.

I have a main table called Master. From there, I have a Customer table, a Platform Table and Application table. The Master has an ID. Customer, Platform and Application records spawned from Master share a common ID (key).

For every record in the Master, there can be:
one corresponding record in the Customer, Platform and or Application tables
OR
many records in Customer, Platform and or Application tables
OR
no records at all.

So for instance when there is no corresponding Platform record, I get no records at all in the report... not even information from the Master. In order to get a record to show in my report, you must have at least one Customer record, Platform record and Application record.

I'd like for my report to show records even when the Customer or Platform or Application tables lack a record. Is this possible? My thinking is it's a join type problem... but I have not been able to figure this out. Thanks for your help.

 
Hi,
Join the tables using a LEFT-OUTER Join from
MASTER --> all the others


But, do not use any selection criteria ( the WHERE... or IN ... clauses in the record selection formula area) on the joined tables as that will change the join type to EQUI and no Master record will be returned is any other table does not have a matching ID.

If you need to do that make the table that needs criteria applied the left-most table.



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
One more question... the report is very close to working.

The Customer table, Platform Table and Application table all have Left Outer Joins to the Master table. All three also have a table joined to them (i.e. Master ->LOJ-> Application->LOJ-> Application Name).

When I use a cross-tab table on the report with data from the Application Table, all is well. But when I also add a cross-tab with Customer data, Application data (sums) are doubled/tripled/etc depending on the number of records in the Customer table.

I have tried several combinations of join types but cannot seem to resolve the issue. I have tried putting the Customer cross-tab in its own section and that does nothing. Any ideas?

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top