[SOLVED] Pull rows where a columns value is in a paragraph

[ryanschefke]

Hello,

 

What is the best query to use optimized for speed that will search a table for rows where the column value is contained in a block of text that is stored in another table?

 

For example, if I am storing "Blue, Yellow, Purple, Green" in one large text value in the "color" column of table "Crayons", how do I SELECT the rows in the "Todays_crayons" table if the value in the "color" column matches any of the values in the "color" column (the text block) of the "Crayons" table?

 

Thanks,

Ryan

[fenway]

Why are you storing it that way/

[ryanschefke]

I don't have to store it that way. I don't know of anyway better. There are 195 countries so the user could have 195 entries in one row of a table. Is that how I should do it? I figured I couldn't store all the users filter in one record because the user could enter an unlimited number of entries and I would just be storing them in randomn columns.

 

There are 52 states so I would likely have the same table with 52 columns

 

....but, how would I compare the entry in one row, say a table named "filter_countries",  with a certain userID to a table called "records" that had a "country" column so I could match only the rows in the "country" column that were contained in the "filter_countries" table with that userID?

[ryanschefke]

Would it be better to store each of the users filters in a "filter_states" table as individual rows vs storing it as a text block then doing a WHERE IN statement?

 

So, for example, I would have a table called "filter_states" with the following columns:

 

    userID    filterState    filterName

row1 33        MI              myFilter

row2 33        OH              myFilter

row3 33        TX              bobsFilter

...  ...        ...

 

How would I compare that to another table that had a bunch of records with a column and I only wanted to display the states that the user had in the "filter_states" table with the filterName myFilter?

[fenway]

How would I compare that to another table that had a bunch of records with a column and I only wanted to display the states that the user had in the "filter_states" table with the filterName myFilter?

You can simply INNER join these two tables.

[ryanschefke]

That worked!