Trouble with logic for Query

[nate6780]

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

[Illusion]

Select * from color_manager WHERE color_friendly_link=ALL(select color_friendly_link from friendly_color where ID='first_drop_down_value' ) AND  patterncolor1='second_drop_down_value' OR patterncolor2='second_drop_down_value' OR patterncolor3='second_drop_down_value';

[nate6780]

Looks close to what I need it to do, however I need my result to come from the swatch_manager table rather than the color_manager.  Can I just swap color_manager with swatch_manager?  Thank so much for your response Illusion.  I'm going to research the ALL command a bit more so I can hopefully modify your code a bit and get this thing rolling.

[nate6780]

This might be more clear:

basically a shirt has one base fabric color and can have up to 3 pattern colors.

(i've shortened fabric to f and pattern to p so it'd fit on the screen)

 

swatch_manager table

swatch_id      -      swatch_f1      -      swatch_p1    -      swatch_p2      -      swatch_p3

1                  -      2 (pink)          -      3 (light blue) -      2  (pink)          -      0 (no third pattern color)

2                  -      3 (light blue)  -      5 (light pink)  -      4  (periwinkle)  -      3 (light blue)

 

color_manager table

color_id        -      color_name      -      color_friendly_link

1                  -      burnt orange  -      1

2                  -      pink              -      5

3                  -      light blue        -      7

4                  -      periwinkle      -      7

5                  -      light pink        -      5

 

friendly_colors table

friendly_id      -      friendly_name

1                  -      orange

2                  -      green

3                  -      yellow

4                  -      brown

5                  -      pink

6                  -      white

7                  -      blue

 

So basically I need to pull a swatch out of the swatch_manager table where the user chooses a friendly_id for a fabric color and a friendly_id for pattern color (f1) which should match any of the 3 pattern fields (p1, p2, p3) within the swatch_manager database.  Sorry if I'm making this more difficult than it need be...

[nate6780]

I'm sorry... the last paragraph in my last post should read as follows...

 

So basically I need to pull a swatch out of the swatch_manager table where the user chooses a friendly_id from a fabric color dropdown which should match the fabric color field (f1) and a friendly_id from a pattern color dropdown which should match any of the 3 pattern fields (p1, p2, p3) within the swatch_manager database.  Sorry if I'm making this more difficult than it need be...

[Illusion]

Try these

Select swatch_id,swatch_f1,IF(swatch_p1='$second_drop_down_value',swatch_p1,IF(swatch_p2='$second_drop_down_value',swatch_p2,IF(swatch_p3='$second_drop_down_value',swatch_p3, 'NO MATCHES'))) from swatch_manager where  swatch_f1=ALL(select color_name FROM color_friendly_link='$first_drop_down_value';

or

Select  swatch_id,swatch_f1, (CASE  '$second_drop_down_value' WHEN '$second_drop_down_value'=swatch_p1 THEN swatch_p1 WHEN '$second_drop_down_value'=swatch_p2 THEN swatch_p2 WHEN '$second_drop_down_value'=swatch_p3 THEN swatch_p3 END) from swatch_manager where  swatch_f1=ALL(select color_name FROM color_friendly_link='$first_drop_down_value';