MySQL Grouping results

Question

My tables

Table function
id function users
-----------------
1  f1       1,2
2  f2       2,3
3  f1       4,5
4  f2

Table users
id name
-------
1  Mark
2  Louis
3  John
4  Denver
5  Nat

I need to write a query that produces the following results:

 f1, Mark, Louis
 f2, Louis, John
 f1, Denver, Nat
 f2

Thanks for any help and excuse my poor english.

Answer 1

Here is one option using find_in_set() and group_concat():

select f.function, group_concat(u.name order by find_in_set(u.id, f.users)) user_names
from `function` f
left join users u on find_in_set(u.id, f.users)
group by f.id

Notes:

• function is a language keyword, hence not a good choice for an identifier (column or table name)

• although what you ask for is possible here, storing CSV data in a table is bad practice and should almost always be avoided - see Is storing a delimited list in a database column really that bad?

Demo on DB Fiddle:

function | user_names
:------- | :---------
f1       | Mark,Louis
f2       | Louis,John
f1       | Denver,Nat
f2       | null