0

SELECT DISTINCT example_type FROM Table_name WHERE 80 IN (example_type);

The values I'm using are 66 and 80.

The values for the column are sometimes "66,80" and sometimes "80,66".

If it is a search for 80, it will only return a match if the column is "80,66" (or "80").

If it is a search for 66, it will only return a match if the column is "66,80" (or "66").

The column type is set to TEXT.

The following works as expected: (both are a match)

....WHERE 80 IN (66,80)...

....WHERE 80 IN (80,66)...

I'd like to keep the contents of the column as CSV.

What do I need to do to the MySQL query to get it work no matter the order of the CSV?

Luke Wenke
  • 1,149
  • 2
  • 23
  • 43
  • I would bite the bullet and save your data right. Whether it be [Junction Tables](http://stackoverflow.com/a/32620163) with many to many, or association tables. Otherwise, development and performance can be a nightmare. – Drew Sep 09 '16 at 03:42
  • It looks like I found a fix. CSV simplifies the queries and the CSV can be easily sent to and from "select" form elements. – Luke Wenke Sep 09 '16 at 03:44
  • The problem with that is non-use of indexes. – Drew Sep 09 '16 at 03:45
  • The special cases are rare and it seems to run fast enough. – Luke Wenke Sep 09 '16 at 03:53

2 Answers2

0

May be, you can use the following where clause:

 WHERE column_name like '%80%'

If your two queries are right, then you can use union clause in mysql

 SELECT ... FROM ... WHERE (your first condition)
 UNION
 SELECT ... FROM ... WHERE (your second condition)
Moises Quintero Orea
  • 76
  • 8
0

SELECT DISTINCT example_type FROM Table_name WHERE FIND_IN_SET(80, example_type);

This is based on the final code:

        WHERE ...a.is_special = 1
            AND (a.foo_list = '' OR FIND_IN_SET($foo_id, a.foo_list))
            AND (a.bar_list = '' OR FIND_IN_SET($bar_id, a.bar_list))
            AND (a.baz_list = '' OR FIND_IN_SET($baz_id, a.baz_list))";
Luke Wenke
  • 1,149
  • 2
  • 23
  • 43