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.

What's the value like in up.location_ids field? – SMA Dec 02 '14 at 08:36 — SMA, Dec 02 '14 at 08:36
@almasshaikh my value is 10,27,36 in up.location_ids field – mizan3008 Dec 02 '14 at 08:46 — mizan3008, Dec 02 '14 at 08:46

Saharsh Shah · Accepted Answer · 2014-12-02T08:53:31.647

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;

edited Dec 02 '14 at 08:53

answered Dec 02 '14 at 08:46

Saharsh Shah

28,687
8
48
83

yes, your second solution is working, will you give me some clarification of this query? – mizan3008 Dec 02 '14 at 08:52
@mizan3008 IN operator required values you can't specify string instead of different values. To achieve this in MySQL you can use FIND_IN_SET() function – Saharsh Shah Dec 02 '14 at 08:54

score 0 · Answer 2 · answered Dec 02 '14 at 08:34

0

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

answered Dec 02 '14 at 08:34

Aditya

1,241
5
19
29

SubQuery using IN operator

2 Answers2