mysql list of variables with regex-CodePudding

My question :

for each of the id in space_id table, i want to get the list of batch id's in batch_ids table

space_id table

 ------- 
|   id  |
 ------- 
| spa_1 |
| spa_2 |
| spa_3 |
| spa_4 |
| spa_5 |
 ------- 


batch_ids table

 ------------ --------------- 
|  batch_id  | request_id    |
 ------------ --------------- 
| 1          |task_spa_1_task|
| 2          |task_spa_2_task|
| 3          |task_spa_3_task|
| 4          |task_spa_4_task|
| 5          |task_spa_5_task|
| 6          |task_spa_6_task|
| 7          |task_spa_2_task|
| 8          |task_spa_3_task|
| 9          |task_spa_9_task|
|10          |task_spa_8_task|
 ------------ ---------------

so my expected output should be batch_id = [1,2,3,4,5,7,8]

 ------------ --------------- 
|  batch_id  | request_id    |
 ------------ --------------- 
| 1          |task_spa_1_task|
| 2          |task_spa_2_task|
| 3          |task_spa_3_task|
| 4          |task_spa_4_task|
| 5          |task_spa_5_task|
| 7          |task_spa_2_task|
| 8          |task_spa_3_task|
 ------------ ---------------

What i've tried

with space_ids AS(
  select id from space_id
)

  select batch_id 
    from batch_ids 
    where request_id  like concat((select id from space_ids), '%')

returns me Single-row subquery returns more than one row. I know this is not the right query but any help by mysql gurus would be appreciated

CodePudding user response：

You can use group_concat.

select group_concat(b.batch_id) as batch_id 
from space_id s 
INNER JOIN batch_ids b ON b.request_id LIKE CONCAT('%',s.id,'%');

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4fb56d2f567792180a3e3250d3a063c5

If you want to include [ in the result use:

select concat('[',group_concat(b.batch_id),']') as batch_id 
from space_id s 
INNER JOIN batch_ids b ON b.request_id LIKE CONCAT('%',s.id,'%');

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=165c3029fc4253a393367cef7f768c89