CODEWITHSUNDEEP
mysqlsqlselectin-operatorfind-in-set
mizan3008
mizan3008

Reputation: 379

SubQuery using IN operator

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.

Upvotes: 1

Views: 114

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

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;

Upvotes: 1

Aditya
Aditya

Reputation: 1261

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

Upvotes: 0

Related Questions