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]

Reply via email to