[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?