2

This dummy table:

name | lesson_mode
-------------------

a    | 1,2,3
b    | 2,3 
c    | 2
d    | 3

Using this query:

SELECT * FROM `cc_teacher` WHERE lesson_mode IN (2,3)

I get

name | lesson_mode
-------------------

b    | 2,3 
c    | 2
d    | 3

I am having a problem in search results, my problem is that suppose, in this case we are talking about lesson_mode column, I have written the SQL query like this: 

SELECT * FROM `cc_teacher` WHERE lesson_mode IN (2,3)

but I didn't get the row in which have 1,2,3, so please help me. Anyone know how to do that?

sjagr
  • 15,983
  • 5
  • 40
  • 67
Ganesh Pawar
  • 187
  • 3
  • 11
  • Please don't make drastic edits that worsen the quality of your question. – sjagr Dec 20 '14 at 14:42
  • 1
    What type is the `lesson_mode` column? You've tagged your question with both MySQL and SQL Server -- those are different SQL servers, which one are you using? – vicvicvic Dec 20 '14 at 14:43
  • 1
    @vicvicvic Since he tagged `php` as well, I'm going to say MySQL (not saying that you can't use SQL server with PHP, but I doubt that OP would) – sjagr Dec 20 '14 at 14:43
  • this should be useful to you i guess : http://stackoverflow.com/questions/4155873/find-in-set-vs-in – Codeek Dec 20 '14 at 14:44

1 Answers1

4

The best help for you is advice to fix your data structure. You should have a table that is TeacherLessons with one row per teacher and one row per lesson. SQL has a great data structure for storing lists. It is called a "table", not a "string". And, worse, you are storing numeric ids as character strings.

You can do what you want using find_in_set(). It would look like:

select *
from cc_teacher
where find_in_set(2, lesson_mode) > 0 or
      find_in_set(3, lesson_mode) > 0;

But my main advice is for your to fix our database structure by introducing a junction table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thanks for your answers but one more issue occured if i have multople columns like same as lesson_mode in that time case what can i do – Ganesh Pawar Dec 20 '14 at 14:48
  • @ganesh . . . You would have multiple junction tables, one for each list. Also, you can ask another question, because this question is specifically about one column. – Gordon Linoff Dec 20 '14 at 14:53
  • @ganesh Add more where conditions, or actually follow good advice and take Gordon's first suggestion. – sjagr Dec 20 '14 at 14:53
  • @sjagr . . . This *is* MySQL specific. The generic approach would use `like`, but there is no reason to use that. – Gordon Linoff Dec 20 '14 at 14:53
  • @GordonLinoff Apologies, I was thrown off the scent when you said "SQL has a..." – sjagr Dec 20 '14 at 14:55