Home » SQL & PL/SQL » SQL & PL/SQL » How to avoid a cartesian product?
How to avoid a cartesian product? [message #21245] Fri, 19 July 2002 07:22 Go to next message
Swanand
Messages: 1
Registered: July 2002
Junior Member
Hi Folks,
I am trying to build query on multiple tables.
I have created a view based on the multiple tables(8).
I have following columns in the view-CATEGORY,SALES_OFFICE,SALES_GROUP,STARTWEEK,ENDWEEK,
DURATION and PRICE.
If CATEGORY,SALES_OFFICE,SALES_GROUP,STARTWEEK,ENDWEEK and DURATION is same for multiple records(it would occur max 4 occurencesie 4 records) then it should be displayed in the following way:-

CATEGORY SALESOFFICE SALESGROUP STARTWEEK ENDWEEK DURATION PRICE1 PRICE2 PRICE3 PRICE4.

Also the records should not be duplicated.I tried to generate query to display data in the above format but,I get a cartesian product.Any thoughts on how to go ahead with it?
Thanks,
Swanand
Re: How to avoid a cartesian product? [message #21247 is a reply to message #21245] Fri, 19 July 2002 13:20 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
First you need to know which columns are unique or primary keys and you need to know how thae tables are related.

Start by sketching the 8 tables with and draw in the joins you are using (from the PK/unique to the foreign keys). Generally - if you have 8 tables - you would have at least 7 joins. If you have a table with a single row in it, then ommiting the join to that table will not cause a cartesian product (e.g. dual).
Previous Topic: Re: to_date from character field yyyymmdd
Next Topic: Problem with Decode Function used with Date
Goto Forum:
  


Current Time: Thu Mar 28 06:32:58 CDT 2024