MySQL, concat by join table with in clauses

Question

Is there any way to join two tables and get group_concat? I have two tables, the 1st one with id and name with the following data:

id	name
1	John
2	Mark
3	Helen
4	Simon

and the 2nd table with the following data:

id	day	team	amount
1	mon	2,4	100.00
2	mon	1,4	80.00
3	fri	2,3	150.00
4	sun	1,3,4	120.00
5	wen	3	40.00

the team field has members ids in comma separated string.

How to concatenate the names, separated by commas in one column?

Required result:

id	day	team	amount
1	mon	Mark, Simon	100.00
2	mon	John, Simon	80.00
3	fri	Mark, Helen	150.00
4	sun	John, Helen, Simon	120.00
5	wen	Helan	40.00

I tried group_concat like the following code, but it did not work:

select b.*, (select group_concat(a.name separeted ',')
from a where a.id in (b.team)) as teamname from b

Any suggestions?

Thanks in advance for your help.

score 2 · Accepted Answer · answered Feb 13 '21 at 21:57

Use FIND_IN_SET instead of IN

CREATE TABLE a
    (`id` int, `name` varchar(5))
;
    
INSERT INTO a
    (`id`, `name`)
VALUES
    (1, 'John'),
    (2, 'Mark'),
    (3, 'Helen'),
    (4, 'Simon')
;

CREATE TABLE b
    (`id` int, `day` varchar(3), `team` varchar(5), `amount` int)
;
    
INSERT INTO b
    (`id`, `day`, `team`, `amount`)
VALUES
    (1, 'mon', '2,4', 100.00),
    (2, 'mon', '1,4', 80.00),
    (3, 'fri', '2,3', 150.00),
    (4, 'sun', '1,3,4', 120.00),
    (5, 'wen', '3', 40.00)
;

select 
b.*
, (select group_concat(a.name ) 
from a 
where FIND_IN_SET(a.id, b.team) > 0) as teamname 
from b

id | day | team  | amount | teamname        
-: | :-- | :---- | -----: | :---------------
 1 | mon | 2,4   |    100 | Mark,Simon      
 2 | mon | 1,4   |     80 | John,Simon      
 3 | fri | 2,3   |    150 | Mark,Helen      
 4 | sun | 1,3,4 |    120 | John,Helen,Simon
 5 | wen | 3     |     40 | Helen

db<>fiddle here

MySQL, concat by join table with in clauses

1 Answers1