Take the 2-minute tour ×

I am running a query where you search an array value. I found a solution as to how to do this on Stackoverflow, however, it does not seem to give the output I would expect. Here are the examples, can you tell me where I am going wrong please:

I have a table with a column called departments and some of the examples rows contain values like:

1: 2,4
2: 1,7,2,8,9,4
3: 4, 2

I run a query on this:

SELECT * FROM udf_multi_value WHERE udfdefid =  '1' AND department IN ( 1, 7, 2, 8, 9, 4 );

This returns all rows in the results, which is what I would expect. However, when I run:

 SELECT * FROM udf_multi_value WHERE udfdefid =  '1' AND department IN ( 2, 4 ) 

it only returns row 1 and 3. As 2 and 4 are in all rows I wuld ave thought it should return row 2 as well.

Any help greatly appreciated.

share|improve this question
Normalize the tables? Look into FIND_IN_SET –  FreshPrinceOfSO Jun 4 '13 at 16:16
I should probably add that all rows have the udfdefid value of 1 so this is not the problem. –  Mediatomcat Jun 4 '13 at 16:16
SHOW CREATE TABLE udf_multi_value; –  Paul Dixon Jun 4 '13 at 16:17
CREATE TABLE udf_multi_value ( id int(11) NOT NULL AUTO_INCREMENT, registrantid varchar(350) NOT NULL, department varchar(350) DEFAULT NULL, udfdefid varchar(255) NOT NULL, value text NOT NULL, isdefault tinyint(4) DEFAULT '0', PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 –  Mediatomcat Jun 4 '13 at 16:21
add comment

1 Answer

Are those single fields with comma-separated values? If so, then you're getting expected behavior. You're passing in the integers 2 and 4, and comparing them against char/varchar fields, e.g.

if (2 == '2,4')
if (4 == '2,4')

MySQL will convert the varchar field to an int, meaning 2,4 becomes int 2 and 4,2 becomes int 4, so the matches succeed.

But 1,7,2,... will be cast to just 1, so there's no match.

You need to normalize your table so each of those numbers is in its own record in a sub-table, or use the MySQL non-standard SQL function find_in_set()

... AND (FIND_IN_SET('2', department) OR FIND_IN_SET('4', department))
share|improve this answer
Yup, this could work. A little function to build the query each time. I will give that a try. Thanks. –  Mediatomcat Jun 4 '13 at 16:23
Awesome. It works. Thanks for the help. –  Mediatomcat Jun 4 '13 at 16:31
add comment

Your Answer


