3

I am using the following query to filter records from my table:

SELECT * FROM teacher_info WHERE teaching_locations in ('Salt Lake City');

It's returning an empty result even though I have the following values stored in the table in the teaching_locations column: newyork,Salt Lake City,los angeles.

I have tried using others values and it's working fine with other values except the values having whitespaces in between. For instance, the query is working fine if the column value is 'newyork'.

Morpheus
  • 1,616
  • 1
  • 21
  • 31
user2623213
  • 233
  • 2
  • 4
  • 10
  • Your query working fine for me. http://sqlfiddle.com/#!9/2f11a/2 – Pardeep Dhingra Dec 19 '15 at 06:15
  • There is a related question which might be useful to you - [useful question](http://stackoverflow.com/questions/4155873/find-in-set-vs-in) – Sabir Khan Dec 19 '15 at 08:08
  • This is what happens when you store multiple values in a single column -- that is not the relational way. Use a junction table and your queries will be simpler and faster. – Gordon Linoff Dec 19 '15 at 12:23

1 Answers1

0

Use FIND_IN_SET() instead of IN operator.

Try this: 

SELECT * 
FROM `teacher_info` 
WHERE FIND_IN_SET('Salt Lake City', teaching_locations);
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • what if there are multiple values?for example i want to find new jersey,new york and salt lake city in teaching_locations – user2623213 Dec 19 '15 at 07:58