Select from a range of categories

Question

Possible Duplicate:
Mysql WHERE problem with comma-separated list

I have the next info in a table

name     categories
------   -----------
John      1,4
Jim       4,1
JAck      4,1

between other.

I want to select all the rows that pertain to category 4, but this statement..

SELECT name, categories
FROM `mytable`
WHERE 4 IN (categories)

returns only "Jim" and "Jack" but not "John". What am I doing wrong?

Edit: Sadly I cant change the structure of the table. I need to use that comma-list style

You need to stop storing more than one value in a single column. — OMG Ponies, Nov 28 '12 at 15:04

Taryn · Accepted Answer · 2012-11-28T15:11:31.860

0

I am not sure why you are storing a comma separated list in a single column. But it you cannot change the table structure, you will want to use LIKE to compare the data:

select name, categories
from yourtable
where categories like '%4%'

See SQL Fiddle with Demo

Or you can use the FIND_IN_SET function:

select name, categories
from yourtable
where FIND_IN_SET(4,categories) > 0

See SQL Fiddle with Demo

My suggestion would be to change your table to not store your data in this comma-separated format, it will cause nothing but problems.

edited Nov 28 '12 at 15:11

answered Nov 28 '12 at 15:05

Taryn

242,637
56
362
405

That will match on 4, 44, etc. – OMG Ponies Nov 28 '12 at 15:07
@OMGPonies yes with `LIKE` that is a problem. Added a query using `FIND_IN_SET` that will solve that issue – Taryn Nov 28 '12 at 15:10
FIND IN SET was the solution :) Thanks! – Enrique Moreno Tent Nov 28 '12 at 15:13
@Dbugger glad you got it working. – Taryn Nov 28 '12 at 15:16

Select from a range of categories

1 Answers1