Thanks for your reply Jeff. While your query is more efficient than the one we had, it still poses the fundamental problem we were trying to avoid; that is returning a "row" for each option.
Our "item" table contains over 280 fields which about 30 of them are multiple look-ups like the one I described. While we obviously don't return 280 fields in our search results page, the very reason for this re-organization is to provide for a better results page and even more importantly, allowing the client to choose his/her own. Now assume the client chooses to display 50 records with 10 multi look-up fields which each have 5 options. We are now returning 2500 rows which we still must combine to display "50" complete results. We are basically doing that now, however we are simply avoiding extra overhead by querying the multi look-up fields first, combining them, and then adding them to the second query which contains all the other 'item' information. It seems to us that there has to be a better way of doing this :) What we envision is a query to return something similar to this: +-------------+------------------+--------------------+ | Item Number | Item Description | Option Description | +-------------+------------------+--------------------+ | 1 | car1 | engine, seat, wheel| | 2 | car2 | seat, doors | | 3 | car3 | steering wheel | +-------------+------------------+--------------------+ TIA, JAMES -----Original Message----- From: Jeff Shapiro Sent: Thursday, May 29, 2003 9:32 AM James, What you are describing is a many-to-many relationship and is very commonly done. You should be able to get your desired output using your existing structure with something like: SELECT i.item_id AS "Item Number", i.item_desc AS "Item Description", o.option_desc AS "Option Description" FROM items AS i, options AS o, items_to_options AS ito WHERE i.item_id = ito.item_id AND ito.option_id = o.option_id ORDER BY i.item_id, o.option_desc; This produces the following: +-------------+------------------+--------------------+ | Item Number | Item Description | Option Description | +-------------+------------------+--------------------+ | 1 | car1 | engine | | 1 | car1 | seat | | 1 | car1 | steering wheel | | 1 | car1 | tires | | 2 | car2 | engine | | 2 | car2 | steering wheel | | 2 | car2 | tires | | 3 | car3 | seat | | 3 | car3 | steering wheel | | 3 | car3 | tires | +-------------+------------------+--------------------+ 10 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]