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)
You may want to consider using InnoDB tables instead of MyISAM tables
for Foreign Key support. Take a look at:
http://www.mysql.com/doc/en/Table_types.html
To retrieve data from more than one table at a time, check out:
http://www.mysql.com/doc/en/JOIN.html
On Thu, 29 May 2003 00:10:49 -0600, James wrote:
> Hello,
>
> Our company is currently redesigning its MySQL database to be more
> efficient. Right now, our major concern is how to effectively do
> multiple value lookups in one value table without sacrificing our
> searching capability.
>
> Basically, we are trying to link an "Item" from one table to multiple
> "Options" in another table without replicating too much data. Item 1
> (lets say car1) has options 1, 2, 3 & 4 (doors, wheels, engine, sunroof)
> for example. Our current system (see below for an example) has a table
> in the middle that acts a reference table of sorts, which works alright,
> but we are currently doing two or more queries at run time; One to
> select and compile the multiple options into a single text string, and
> one to select the rest of the items info. Then we combine both queries
> and fire them out to a web page.
>
> What we would like to see is a single MySQL select query that joins the
> data for us into another string using the 'AS' name construct (not
> necessarily with the current database structure, we are open to all
> options at this point). If that is not possible (or recommended) any
> suggestions on how to improve & re-organize for efficiency is greatly
> appreciated.
>
> Example of existing system:
>
> Table item
> ------------
> item_id - 1
> item_desc - car1
>
> Table item_TO_options
> -----------------------------
> item_id
> option_id
>
> Data for Table item_TO_options
> ----------------------------------------
> item_id | option_id
> 1....1
> 1... 2
> 1... 3
> 1....4
>
>
> Table options
> -----------------
> option_id
> option_desc
---
Listserv only address.
Jeff Shapiro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
