MySql Select inside Select with Operator IN

Question

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  |

this violates 3rd normal form of a database by having multiple values in 1 column. Logically one should have a group_users table which links each user to a group. Is this a design change which can be made or MUST you use your existing structure/layout? — xQbert, Mar 16 '22 at 20:42
Does the order of the users in the list matter in relation to the order in table group? and what data type is "id_user" in your group table? is it a [SET Type](https://dev.mysql.com/doc/refman/8.0/en/set.html)? — xQbert, Mar 16 '22 at 20:50
@xQbert the order isn't important... id_user in table Group is a varchar... i know they should be foreign keys of table User but I cannot change the existing structure — Wall3-8e, Mar 16 '22 at 21:05

score 1 · Accepted Answer · answered Mar 16 '22 at 21:10

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

Good catch. if we use the other example peter shows up – xQbert Mar 16 '22 at 21:14 — xQbert, Mar 16 '22 at 21:14

Nayanish Damania · Answer 2 · 2022-03-16T21:11:28.303

0

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

Or

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

edited Mar 16 '22 at 21:11

answered Mar 16 '22 at 20:40

Nayanish Damania

542
5
13

Not sure if it matters but the names list of names generated may not align to the order in the id_user https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4be033b845ade160298d9edaac594b24 Note in my example: James ID is 4 so I would expect him last on the list generated: but it may not matter to the OP. Also `group` is a reserved word and as a table name would need to be escaped using backtick – xQbert Mar 16 '22 at 20:47
@nayanish I try your example and works if i use inside IN(1,2,3,4) but if i use IN(id_user) not works....I doubt that it cannot be done :( – Wall3-8e Mar 16 '22 at 20:58
@Wall3-8e it works look at the dbfiddle.uk link in comments. However that is version 8 of mySQL; and we do not know your version. – xQbert Mar 16 '22 at 21:02
@xQbert Modified the query can you please try again – Nayanish Damania Mar 16 '22 at 21:07
@NayanishDamania adding a.id_user in your top query is wrong. it worked before just not with order and since order isn't important: Id' stick with your original: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5b76f3fbe896d2befae9edbb8fbfcf38 but again it depends on RDBMS version. works in 8 not in 5.6 *note I had to escape the 'Group' keyword in the linked demo – xQbert Mar 16 '22 at 21:10
@xQbert I have seen that the example works... my system use MariaDb... it only takes the first name like second example https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5b76f3fbe896d2befae9edbb8fbfcf38 ... do you know why? – Wall3-8e Mar 16 '22 at 21:26
1

Id use the find_In_set approach. This approach doesn't handle the where clause by group. As to why this isn't working, I'd have to research. The `find_in_set` approach seems correct to me . – xQbert Mar 16 '22 at 21:28

MySql Select inside Select with Operator IN

2 Answers2