SubQuery using IN operator

Question

I have a table named 'user_permission' there is a column named location_ids and data type is varchar(255). I stored here value like 10,27,36. Now I want to use this 'location_ids' in a IN comparison operator. The following query I have tried, but I did not get my expected result.

SELECT (SELECT GROUP_CONCAT( `name` SEPARATOR ',' ) as name FROM location WHERE `remove` = 0 AND id IN(up.location_ids)) AS name FROM user_permission AS up

but if I provide IN(10,27,36) instead of IN(up.location_ids) then it's working.

Answer 1

Use FIND_IN_SET() function

Try this:

SELECT up.id, GROUP_CONCAT(l.name) AS `name`
FROM user_permission AS up 
LEFT JOIN location l ON FIND_IN_SET(l.id, up.location_ids) AND l.remove = 0
GROUP BY up.id;

OR

SELECT (SELECT GROUP_CONCAT(`name` SEPARATOR ',') AS NAME 
        FROM location 
        WHERE `remove` = 0 AND FIND_IN_SET(id,up.location_ids)
      ) AS NAME 
FROM user_permission AS up;

Answer 2

See if this works

SELECT (SELECT GROUP_CONCAT( `name` SEPARATOR ',' ) as name FROM location WHERE `remove` = 0 AND id IN(select location_ids from user_permission)) AS name FROM user_permission