Subquery with multiple output not outputting data?

Question

MySQL

The point is the following. There are two tables. In one text field, in which a string of the form
,1,3,4,
AND is stored, there is a second table where there are values ​​of these keys.

SELECT RIGHT(LEFT(a.melody, LENGTH(a.melody)-1),LENGTH(a.melody)-2) AS melodyId, //обрезаю запятые по краям
,(SELECT GROUP_CONCAT(amelody.`name` SEPARATOR ', ') FROM amelody WHERE amelody.id IN (melodyId)) as melodyInfo
FROM
a

If in the subquery instead of IN(melodyId) you write (1,3,4) - everything works fine, but with the value IN(melodyId) only the single result of the key 1 is returned.
melodyId exactly stores the value
1,3,4 ? Where to dig?

Answer 1

melodyId does not store a list (1,3,4), but a string '1,3,4'. These are completely different things. Bring the base to normal form , move this field to a separate many-to-many relationship table.