How to avoid a cartesian product? [message #21245] |
Fri, 19 July 2002 07:22 |
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 |
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).
|
|
|