Agfdhyk

I have an issue with SQL IN query: I'm storing multiple employee IDs in a table, separated with commas for each task. When I try to fetch a task with an IN query, I'm not getting the row which contains the employee IDs.

My query is:

select 

   t.*, 

   e.emp_name, 

   d.department_name 

from 

   task as t 

   LEFT JOIN employee as e on(e.emp_id=t.assign_to) 

   LEFT JOIN department as d on(d.depart_id=e.depart_id) 

where 

   t.task_status='PENDING' 

   AND t.created_by!='31' 

   AND t.assign_to IN ('31') 

order by 

   t.task_id DESC

The stored value in database

IN doesn't work like that

Example if your data looks like:

ManagerName, ManagerOf

'John', 'Steve,Sarah,Dave'

You can't do:

SELECT * FROM managers WHERE 'sarah' IN ManagerOf

IN is best conceived as an extension of OR:

SELECT * FROM managers WHERE managerof IN ('Sarah','Steve')

--is the same as:

SELECT * FROM managers WHERE 

  managerof = 'Sarah' OR

  managerof = 'Steve'

There would be as many OR clauses as there are items in the IN list.

Hopefully this shows you why the database doesn't return you any results.. Because a value of Steve,Sarah,Dave is not equal to either Sarah or Steve. The database doesn't look at the commas and say "oh, that's a list" - it's just a single string of characters that happens to have a comma character every now and then

There are nasty quick hacks to so-so achieve what you want, using LIKE and string concat but they aren't worthy of an answer, to be honest

You need to change your database structure to include a new table that tracks the task id and the employee(s) id it is/was assigned to. After doing that, you'll be able to use IN on the employee id column as you're expecting to with this query