-1

Task Table

id      assign_to       task
1       1,2,4           Development
2       2,5,3           Testing

Employee Table

id      Name
1       Dhawan
2       Sachin
3       Dhoni
4       Virat
5       Rohit

Here i am trying to get all employee name from employee table and get in assign_to field in task table. so my output will be below

id      assign_to               task
1       Dhawan, Sachin,Virat    Development
2       Virat, Rohit, Dhoni     Testing

Below query i wrote. Can anyone suggest me what will be the query for this

select t.*,  from task t inner join employee e on e.id in (t.assign_to)
C B
  • 1,677
  • 6
  • 18
  • 20
Sangram Badi
  • 4,054
  • 9
  • 45
  • 78
  • 6
    Don't store your data like that. You should look up normalizing your data. This will lead to nothing but headaches. – dfundako Sep 18 '18 at 15:49
  • @dfundako so how can we store ? directly by name ? – Sangram Badi Sep 18 '18 at 15:50
  • 5
    Have a task table, an employee table, and then a ManyToMany table that relates task id's to employee id's. – dfundako Sep 18 '18 at 15:51
  • The best way is to avoid save like comma separated. – Gufran Hasan Sep 18 '18 at 15:52
  • 1
    See also my answer to this past question: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Sep 18 '18 at 16:00

2 Answers2

1

Updated your query as:

 select t.*,  from task t inner join 
employee e on FIND_IN_SET(e.id, t.assign_to) > 0 
GROUP BY e.id
Gufran Hasan
  • 8,910
  • 7
  • 38
  • 51
0

Please try this

select task.id,
(select group_concat(employee.name,',')  
from employee employee  
where  find_in_set(employee.id, task.assign_to)> 0 )  as emp ,   
task.task   
from task task;
nitzien
  • 1,117
  • 9
  • 23