I'd like to find the SQL expression to translate a concatenation of ids into a concatenation of matching labels.
Here is the example :
Table user id name hobbies 1 Mike 2,5,4 2 Paul 1,5 Table hobbies id label 1 tennis 2 cinema 3 entomology 4 travel 5 programming Result expected id name hobbies 1 Mike cinema,programming,travel 2 Paul tennis,programming
I tried doing things with sub requests but couldn't achieve it properly.
After Tims answer, I found out I have problem to search in hobbies properly
SELECT u.id, u.name, GROUP_CONCAT(h.label) AS hobbies FROM user u LEFT JOIN hobbies h ON FIND_IN_SET(h.id, u.hobbies) > 0 WHERE h.label LIKE '%travel%' GROUP BY u.id, u.name ORDER BY u.id;
Doesn't return the proper result :
Result returned id name hobbies 1 Mike travel Result expected id name hobbies 1 Mike cinema,programming,travel