How to aggregate two tables on a comma-separated string field?-CodePudding

In one table I have a column with multiple values separated by a comma. In the second, columns with id and name. I need to get names separated by a slash.

Table1:

id	value1	ids	value2
1	abcd	1,2,3	efgh
2	ijkl	1,4,6	mnop

Table2:

id	name
1	1AB
2	2CD
3	3EF
4	4GH
5	5IJ
6	6KL

SELECT
    a.*,  
    GROUP_CONCAT(b.name,'/') groupedName
FROM
    table1 a
LEFT JOIN
    table2 b ON b.id IN (a.ids)
WHERE
    a.id = 1

Result:

id	value1	ids	value2	groupedName
1	abcd	1,2,3	efgh	1AB/2CD/3EF

CodePudding user response：

Given that your "Table1.ids" has VARCHAR(n) type, you can use the LIKE function for the matching condition on the JOIN between the two tables, only then apply the GROUP_CONCAT aggregation function:

SELECT t1.id,
       t1.value1,
       t1.ids,
       t1.value2,
       GROUP_CONCAT(t2.name, '/') AS groupedName 
FROM       table1 t1
INNER JOIN table2 t2
        ON t1.ids LIKE t2.id || ',%'
        OR t1.ids LIKE  '%,' || t2.id || ',%'
        OR t1.ids LIKE           '%,' || t2.id
GROUP BY t1.id,
         t1.value1,
         t1.ids,
         t1.value2

Check the demo here.