CODEWITHSUNDEEP
mysqlsubquery
Jan
Jan

Reputation: 43169

MySQL Subselect issue

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?

Upvotes: 0

Views: 97

Answers (1)

Michael Krikorev
Michael Krikorev

Reputation: 2156

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

Upvotes: 1

Related Questions