In Array not working as expected

Question

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.

I should probably add that all rows have the udfdefid value of 1 so this is not the problem. — Mediatomcat
– Mediatomcat, Commented Jun 4, 2013 at 16:16
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
– Mediatomcat, Commented Jun 4, 2013 at 16:21

bobwienholt · Accepted Answer · 2013-06-04 19:19:51Z

5

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')
and
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))

edited Jun 4, 2013 at 19:19

bobwienholt

17.6k3 gold badges43 silver badges48 bronze badges

answered Jun 4, 2013 at 16:18

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mediatomcat Over a year ago

Yup, this could work. A little function to build the query each time. I will give that a try. Thanks.

Collectives™ on Stack Overflow

In Array not working as expected

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related