Problem with Multiple Join

All,

   Environment: Oracle 8i

   I have a question concerning a problem I have been trying to solve for several weeks.

I am trying to write a query to report on sales figures. Without going into extreme details, I have 4 basic tables: Orders, Order_items, Sales_reps, and Sales_branches.

All have order_id as a key and I am trying to collect all information with one query. Orders is 1:1 to sales_branches, 1:M to order_items, and 1:M to Sales_reps.

I have written a query that joins Orders, Order_items, and sales_branches which works fine. I would also like to obtain sales reps but when I join that table as well I of course get the product of items and sales_reps, in other words if an order has 2 sales reps, I get twice as many line items, 3 sales reps 3x the line items etc.

I solved the problem by writing a function that returned a comma-delimited list of the sales reps for a given order as a column in my original query, now the problem is that I cannot search in my WHERE clause for sales_rep LIKE XXX as Oracle tells me it is an invalid column (I guess since it is derived from a function?).

Am I going about this the wrong way, am I missing and easier solution?

Any help greatly appreciated.

Dirk Meilinger Received on Tue Aug 28 2001 - 19:56:14 CDT