cursor in mysql for row having multiple values in it-CodePudding

I have table emails_grouping in that I have one column named 'to_ids' this column contains multiple employee Id's . Now I want to change that Id's with respective employee names. employee data is in employee table. this is in mysql.

I tried multiple ways but I'm not able to replace id's with names because , that 'to_ids' column contains multiple 'Ids'.

 description                                        to_ids                     
'Inactive Employees with missing Last working day', '11041,11109,899,13375,1715,1026'

above is the column which I want to change Id's with employee names.

CodePudding user response：

This problem should demonstrate to you why it's a bad idea to store "lists" of id's like you're doing. You should instead store one id per row.

You can join to your employee table like this:

SELECT e.name
FROM emails_grouping AS g
JOIN employee AS e
  ON FIND_IN_SET(e.id, g.to_ids)
WHERE g.description = 'Inactive Employees with missing Last working day';

But be aware that joining using a function like this is not possible to optimize. It will have very slow performance, because it can't look up the respective employee id's using an index. It has to do a table-scan of the employee table, and evaluate the id's against your comma-separated list one by one.

This is just one reason why using comma-separated lists instead of normal columns is trouble. See my answer to Is storing a delimited list in a database column really that bad?