0

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

Joe McMahon
  • 3,266
  • 21
  • 33
Madhukant Tiwari
  • 1
  • 1

1 Answers1

1

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

Caius Jard
  • 72,509
  • 5
  • 49
  • 80