MySQL Subselect issue

Question

I have an issue with a mysql subselect.

**token table:**
id | token | articles
1  | 12345 | 7,6
2  | 45saf | 6,7,8

**items table:**
id | name                | filename
6  | Some brilliant name | /test/something_useful.mp3
7  | homer simpson       | /test/good-voice.mp3

**query:**
SELECT items.`filename`,items.`name` FROM rm_shop items WHERE items.`id` IN ( SELECT token.`articles` FROM rm_token token WHERE token.`token` = 'token')

I only get one of the two files (with the id 7 that is). What am I missing here?

Answer 1

For a column with concatenated data (like your "articles" column), you can not use MySQL IN() Function. Instead use the string function FIND_IN_SET() to query such values. In your case:

SELECT items.`filename`,items.`name` FROM rm_shop items 
WHERE FIND_IN_SET(items.`id`, 
(SELECT token.`articles` FROM rm_token token WHERE token.`token` = 'token')) > 0

A working sqlfiddle: http://sqlfiddle.com/#!2/796998/3/0