[SOLVED] MySql Select inside Select with Operator IN

Issue

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  |

Solution

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

Answered By – nbk

Answer Checked By – David Marino (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.