2

I am trying to match the text in a column in one table, to part of a string in a column of a second table, if there is a match an image will be output on my webpage. Below is an example

Products

ID | Prod_Code | Colours
1    100         Yellow, Orange, Navy

Colours

ID | Colour | Image Link
1    Yellow   Yellow swatch image
2    Orange   Orange swatch image

What I am trying to achieve is that if the products.colours contains the text "yellow" the yellow swatch image will appear on the screen, same for orange etc.

I know that the table should be normalised for best practice but unfortunately this was not designed by me and there are time constraints.

Any help would be much appreciated.

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
Matt
  • 27
  • 1
  • See the similar question: [Nested MySql Select statement with “where in” clause](http://stackoverflow.com/questions/6667378/nested-mysql-select-statement-with-where-in-clause/6667524#6667524) – ypercubeᵀᴹ Oct 11 '12 at 10:29
  • You can use something like `ON FIND_IN_SET(Colours.colour, Products.Colours)` for the join. – ypercubeᵀᴹ Oct 11 '12 at 10:32

1 Answers1

0

Try something like this:

SELECT p.Prod_Code, c.Image_Link 
FROM Products AS p
  INNER JOIN Colours AS c 
  ON p.Colour LIKE '%' + c.Colour + '%' ;

Although that might go a bit pear shaped if you have colours like YELLOW, YELLOW2, etc.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
Dai
  • 119
  • 1
  • 4
  • I couldn't seem to get this to work, so i have altered my tables i now have a link table like this Product Colour table Prod Num | Colour_ID 1 | 2 1 3 basically each colour_id corresponds to a particularly colour in the "Colours" table, i want to if Product_ID is equal to Product ID in product table, and colour_ID is equal to Colour_ID in colour table, output the colour image stored in the the colour table (i hope this makes sense!) – Matt Oct 15 '12 at 14:31