I've been battling with the below for a few days now and cannot reach a simple solution! I'm hoping somebody will be able to point me in the right direction!

I have two tables in my database: A COLOURS table and a PRODUCT table.

The COLOURS table goes along the lines of:

ColourID ColourName
1 Red
2 Green
3 Blue
4 Yellow

In my PRODUCTS table I have a product with the colours listed as comma seperated values, such as:

ProductID ProductName ProductColours
1 Nice Trousers 2,3,4

Ultimately I want to bring back a drop down menu in my form that would show:

2 Green
3 Blue
4 Yellow

for this example.

I'm not sure if I should be retreiving the records and building the above in the SQL (e.g SELECT ColourName FROM ColourTable WHERE ColourID LIKE ..... etc) or if I can build it in ASP.

I have succeeded in bringing back the drop-down menu if I used the ColourName only in the array (e.g Red,Green,Yellow), but now I'm stuck with having to use ID numbers instead, so I need to present the names based on the IDs!

I've searched through Google and this forum and can't find anything usable - will somebody be able to point me in the right direction?

Thank you for your time.