Check if the content of a cell in one table exists in Another Table

I have 2 SQL Tables:

attributesKey

id-------------name

C--------------Crunchy

S--------------Soft

R--------------Round

L--------------Long

produce

item---------attributes

Apple--------C,R

Orange------S,R

Bananna----L,S

Carrot-------L,C

This is a simplification of the issue I'm having开发者_JS百科; But, I think it should highlight the trouble.

Users enter attributes they are looking for (ie. Crunchy) and I should show produce who have the Key value "C" (ie. "Apple, Carrot"). ~Pseudo Code for the SELECT statement ~

SELECT * FROM produce AS p WHERE (attributesKey.name = "Crunchy") AND (p.attributes LIKE attributesKey.id)

I hope that makes sense. I've been looking at it so long, its just become a set of random shapes on the screen.

Thanks

Try the below query -

SELECT * FROM produce p WHERE p.attributes LIKE '%(SELECT a.id from attributesKey a where a.name="Crunchy")%'; 

You can use FIND_IN_SET(str,strlist) like:

SELECT * FROM produce AS p WHERE FIND_IN_SET('C', attributes)

you can also use logical operators like AND or OR.

But a better solution would be to create a third able which represents the connection between the two. (The easiest way if you have numeric keys if your tables)

produce_attributes (produce_id, attributes_id)

and...

SELECT * 
FROM produce AS p
    LEFT JOIN produce_attributes AS pa ON pa.produce_id = p.id
    LEFT JOIN attributesKey AS ak ON ak.id = pa.attributes_id
WHERE ak.name = 'Crunchy'

Edit:

Your attributesKey is called a dictinary table because it only resolves codes to values. And you have a many-to-many relationship between attributesKey and produce. Many-to-many relationships are represented by a connecting table in relational database management (or at least this is the most common representation of it).

So most of all I recommend you numeric keys and a third table.