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.

EDIT :

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

Thanks.

1 Answers

1
Tim Biegeleisen On Best Solutions

You should avoid storing CSV in your tables, but here is one way to proceed:

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
GROUP BY
    u.id,
    u.name
HAVING
    COUNT(CASE WHEN h.label = 'travel' THEN 1 END) > 0
ORDER BY
    u.id;

The magic here is in the join condition, which links a given user record to a record in the hobbies table on the condition that the user has this hobby.