Hi Forum,
I'm having trouble with some logic for doing a query on multiple tables. I feel I could set this up better but I'm not really sure how to go about it, perhaps I'm just fried from working on it so long. Here's my question...
I have a database that holds information about fabric swatches for suits which there will be about 400 of. I created the following tables: swatch_manager, color_manager and friendly_colors. The swatch_manager holds all the information about the swatches including 4 fields which I'm having trouble with: swatch_fabriccolor, swatch_patterncolor1, swatch_patterncolor2, swatch_patterncolor3. The color_manager table has 72 distinct color rows in it which are referred to by each of the 4 fields in the swatch_manager table. Also in the color manager is a field called color_friendly_link which has an ID in it that refers to the friendly_color table. The purpose of the friendly color table is to break down the 72 colors in the color_manager into 11 simplified colors for searching purposes (light_blue, dark_blue, navy would all be identified by the id for "Blue" contained in the simple color table).
Now I have on the front end of the site 2 dropdowns in a form that contain the 11 simple_color ID's. One dropdown is to choose a friendly fabric color, the other is to choose a friendly pattern color and have it search on all three of the 3 pattern fields. The idea is if someone picks a blue fabric color and a pink pattern color, the query will return all matching blue shirts (light blue, dark blue, navy) with all matching pink patterns (light pink, dark pink, med pink). Is this impossible to do in one query? I hope this makes sense and thanks in advanced for any help.
Nate