Problem with MySQL Select query with "IN" condition

Question

I found a weird problem with MySQL select statement having "IN" in where clause:

I am trying this query:

SELECT ads.* 
  FROM advertisement_urls ads 
 WHERE ad_pool_id = 5 
   AND status = 1 
   AND ads.id = 23 
   AND 3 NOT IN (hide_from_publishers) 
ORDER BY rank desc

In above SQL hide_from_publishers is a column of advertisement_urls table, with values as comma separated integers, e.g. 4,2 or 2,7,3 etc.

As a result, if hide_from_publishers contains same above two values, it should return only record for "4,2" but it returns both records

Now, if I change the value of hide_for_columns for second set to 3,2,7 and run the query again, it will return single record which is correct output.

Instead of hide_from_publishers if I use direct values there, i.e. (2,7,3) it does recognize and returns single record.

Any thoughts about this strange problem or am I doing something wrong?

posted query will produce error `status = 1 and and ads.id = 23` double AND — Shakti Singh, Feb 16 '11 at 16:54

score 3 · Answer 1 · answered Feb 16 '11 at 16:57

There is a difference between the tuple (1, 2, 3) and the string "1, 2, 3". The former is three values, the latter is a single string value that just happens to look like three values to human eyes. As far as the DBMS is concerned, it's still a single value.

If you want more than one value associated with a record, you shouldn't be storing it as a comma-separated value within a single field, you should store it in another table and join it. That way the data remains structured and you can use it as part of a query.

score 1 · Accepted Answer · answered Feb 16 '11 at 16:59

1

You need to treat the comma-delimited hide_from_publishers column as a string. You can use the LOCATE function to determine if your value exists in the string.

Note that I've added leading and trailing commas to both strings so that a search for "3" doesn't accidentally match "13".

select ads.* 
    from advertisement_urls ads 
    where ad_pool_id = 5 
        and status = 1 
        and ads.id = 23 
        and locate(',3,', ','+hide_from_publishers+',') = 0
    order by rank desc

answered Feb 16 '11 at 16:59

Joe Stefanelli

132,803
19
237
235

2

Painful, why not `... and not find_in_set(3, hide_from_publishers) ... `? – Marc B Feb 16 '11 at 18:00
@Marc B: Agreed, that would be better for MySQL. Guess I was thinking more generically. – Joe Stefanelli Feb 16 '11 at 18:02
`thinking more generically` - that would lead to a `LIKE` clause not a MySQL `LOCATE` function right? – RichardTheKiwi Feb 16 '11 at 18:34
This did work for me, but with tweak, instead and locate(',3,', ','+hide_from_publishers+',') = 0 I had to use concat: locate(',3,', concat(',',hide_from_publishers,',')) = 0 -- thanks Joe. I will also try with Mark's find_in_set but for now locate seems to have done the trick – Rakesh Feb 17 '11 at 17:48
wow find_in_set also works like charm, thanks got 2 things to learn today about mysql :) – Rakesh Feb 17 '11 at 17:56

score 0 · Answer 3 · edited May 23 '17 at 12:26

0

You need to split the string of values into separate values. See this SO question...

Can Mysql Split a column?

As well as the supplied example...

http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

edited May 23 '17 at 12:26

Community

1
1

answered Feb 16 '11 at 17:05

KOGI

3,959
2
24
36

score 0 · Answer 4 · edited May 23 '17 at 10:34

0

Here is another SO question:

MySQL query finding values in a comma separated string

And the suggested solution:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

edited May 23 '17 at 10:34

Community

1
1

answered Feb 17 '11 at 18:41

KOGI

3,959
2
24
36

Problem with MySQL Select query with "IN" condition

4 Answers4