-1

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.

Stu
  • 30,392
  • 6
  • 14
  • 33
Girish AJ
  • 11
  • 4

1 Answers1

0

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • thanks for suggestion , but I'm having multiple records in this table , for that do I need to write for each g.description ? – Girish AJ Dec 02 '22 at 19:14
  • I'll leave it to you to adapt my example query to your needs. – Bill Karwin Dec 02 '22 at 19:16
  • Can we use cursor in this scenario , I'm just beginner here my senior told me to use cursor. and I'm not understading , how will cursor read multiple value of single row. – Girish AJ Dec 02 '22 at 19:20
  • I would ask your senior to show you an example of what they mean. – Bill Karwin Dec 02 '22 at 19:26
  • they said , we need named instead of id's , and use cursor to do it. it will read values row by row . and if row contain multiple values . split them in single values and then find name WRT id and store that values in temp table. – Girish AJ Dec 02 '22 at 19:30