Date: 10/17/07 (MySQL Communtiy) Keywords: no keywords A company is currently storing one colorID for each product. The colorID is included in the main product_list table. Reporting on different colors is pretty straightforward: SELECT * FROM product_list WHERE colorID=1 OR colorID=2; A list of matches appears, one row per productID. Simple and easy. Now the company wants to add multiple colorIDs per productID. The best way to do this (I think?) is to create a product_color lookup table containing productID and colorID.. This way unlimited numbers of colors could be stored per productID. Alternately, I could add two fields to the product_list table (color_id_2, color_id_3) but I'd really rather not go there. In either case, I have a dilemma. As soon as multiple colorIDs are assigned to a product my simple report is broken. Instead of listing each product separately (along with it's corresponding colorID), identical productIDs are listed for each associated colorID. SELECTIs there an efficient way to retain my product_color lookup table and list all products singularly, with multiple colorIDs on each row? REPORT:
|