MySql Select inside Select with Operator IN-CodePudding

I have 2 tables:

Group

 ------------ ------------ 
|  id_group  |  id_user   |
 ------------ ------------ 
| 1          | 1,2,3,4    |

User

 ------------ ----------- 
|   id_user  |   name    |
 ------------ ----------- 
| 1          | james     |
| 2          | lars      |
| 3          | kirk      |
| 4          | robert    |

I'd like use this query:

select id_group as id, (select name from user where id_user IN (id_user)) as name
from group
where id_group = 1

Result:

 -------- ------------------------- 
|   id   |           name          |
 -------- ------------------------- 
| 1      | james,lars,kirk,robert  |

CodePudding user response：

IN wil not work, as id_user from group will be handled as text, so FIND_IN_SET a better but slow possibility

But you should urgently read Is storing a delimited list in a database column really that bad?

CREATE TABLE `group` (
  `id_group` INTEGER,
  `id_user` VARCHAR(7)
);

INSERT INTO `group`
  (`id_group`, `id_user`)
VALUES
  ('1', '1,2,3,4');

CREATE TABLE user (
  `id_user` INTEGER,
  `name` VARCHAR(6)
);

INSERT INTO user
  (`id_user`, `name`)
VALUES
  ('1', 'james'),
  ('2', 'lars'),
  ('3', 'kirk'),
  ('4', 'robert'),
  ('5', 'peter');

select id_group as id
, (select GROUP_CONCAT(name ORDER BY id_user ASC) from user u where FIND_IN_SET(u.id_user, g.id_user)) as name
from `group` g
where id_group = 1

id | name                  
-: | :---------------------
 1 | james,lars,kirk,robert

db<>fiddle here

CodePudding user response：

Try this:

SELECT 
    id_group AS id, 
    (SELECT GROUP_CONCAT(name) FROM user WHERE id_user IN (id_user)) AS name
FROM group
WHERE a.id_group = 1

This assumes the string has the initial and final single quotes in them.

SELECT 
    id_group AS id, 
    (SELECT 
        GROUP_CONCAT(name) 
     FROM user 
     WHERE id_user IN (REPLACE(id_user,  ',', ''','''))
    ) AS name
FROM group
WHERE a.id_group = 1

If not, you could just add them.

SELECT 
    id_group AS id, 
    (SELECT 
        GROUP_CONCAT(name) 
     FROM user 
     WHERE id_user IN (''''   REPLACE(id_user,  ',', ''',''')   '''')
    ) AS name
FROM group
WHERE id_group = 1