Need help with SELECT

[kwigell]

I have a couple tables in a database. The first table list products that can be dyed with combinations of different color dyes. The second table lists the available dye colors. The second table only has two fields: DyeID (the index) and Color (text description of each available color, e.g. "brown", "indigo", etc.). The first table (the product table) contains for each product the corresponding index numbers from the dye table. So, for example, a typical product record might contain fields containing 3, 7, and 14, which would correspond to the dye index numbers. Each product can use a maximum of six dye colors.

So what I want to do is use a SELECT statement to return all the products in the product table, but with the dye names substituted for the dye index numbers for each product. Of course, "SELECT * FROM Products" gets me just the dye index numbers. "SELECT * FROM Products, Dyes WHERE Products.Dye1 = Dyes.Color" sort of works but only gets me the first dye color for each product. I need to return all the dye colors, but with the text name (from the Dyes table) substituted for each dye index number.

I'm sure there is a very obvious answer to this. Please forgive a rank newbie. TIA.

[phranque]

welcome to WebmasterWorld, kwigell!

the proper way to do this is for your products table to describe products, your dyes table to describe dyes and a third table to associate products and dyes.

therefore your query might look something like:
SELECT Product, Color FROM ProductsDyes, Products, Dyes WHERE ProductsDyes.ProductID = Products.ProductID AND ProductsDyes.DyeID = Dyes.DyeID

then use your application to limit the number of rows per product in ProductsDyes.

[kwigell]

phranque,

Thanks very much! I'll give that a try.

Kevin