Sathvik Cheela Sathvik Cheela - 3 months ago 25
MySQL Question

MySQL split and join the values

I have a table [mapping] with 2 columns similar to below

id | values
1 | 1,2
2 | 1,2,3
3 | 1,1
4 | 1,1,2


and another table [map] is similar to this

sno | values
1 | Test
2 | Hello
3 | Hai


My expected output is

id | values
1 | Test,Hello
2 | Test,Hello,Hai
3 | Test,Test
4 | Test,Test,Hello


Is it possible? If it is please can anybody build a query for me.

Answer

You can use MySQL FIND_IN_SET() to join the tables and GROUP_CONCAT() to concat the values :

SELECT s.sno,GROUP_CONCAT(s.values) as `values` 
FROM mapping t
INNER JOIN map s ON(FIND_IN_SET(s.id,t.values))
GROUP BY s.sno

Note: You should know that this is a very bad DB structure. This may lead to a lot more complicated queries and will force you to over complicate things. You should Normalize your data, split it , and place each ID in a separate record!