2

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?

Improve this question
1
  • posted query will produce error status = 1 and and ads.id = 23 double AND Commented Feb 16, 2011 at 16:54

4 Answers 4

Reset to default
3

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.

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

Comments

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
Improve this answer

5 Comments

Painful, why not ... and not find_in_set(3, hide_from_publishers) ... ?
@Marc B: Agreed, that would be better for MySQL. Guess I was thinking more generically.
thinking more generically - that would lead to a LIKE clause not a MySQL LOCATE function right?
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
wow find_in_set also works like charm, thanks got 2 things to learn today about mysql :)
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/

Improve this answer

Comments

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.