[resolved] product table - colors table subquery questions.

[ZeBula8]

Thinking of how to go about this:

I have a products table with many products.
These products come in various colors.

In my products table, I would like to have one field, [color], that indicates
the various colors this product is available in.

Also, I have a color table, with [colorID], [color] fields.


In the [product].[color] field, I tried inserting data like:
1,2,4,6 and using a subquery such as:
select color from color where colorid in (select color from products where productid = 1)

but with no luck.



My ideal example:
If a user selects a product, a drop down is populated with
all of the available colors.


Wondering how to go about creating the sql for retrieving
this.

Is there another way to do this?

[brucevde]

In my products table, I would like to have one field, [color], that indicates the various colors this product is available in.

Never use a single field to indicate multiple values.

Normalize your database by creating another table called ProductColors (ProductId, ColorId). This allows you to get the colors for a single product and also allows you to get all the products that come in a specific color.

For your dropdown, the sql statement would simply be

Select PC.ProductId, PC.ColorId, C.Color 
From ProductColors PC Inner Join Colors C On C.ColorId = PC.ColorId
Where PC.ProductId = ?

[ZeBula8]

this is what I've been struggling for - very much appreciated brucevde !