sql - Use comma-delimeted values of a column in a query -

i have column in table has comma-delimited values (course ids) trying use in query below.... in (students.courseids)

it not work :-)

if replace column value in(1,3,9) works fine.

thanks in advance help!

select distinct students.student_name, students.grade_id, books.book_title, books.price (books inner join courses on books.course_id = courses.course_id)        inner join students on courses.grade_id = students.grade_id (students.id)=3 , cstr(books.course_id) in(students.courseids); 

the problem have in clause useless in instance. value pulling single value, far engine concerned, looking 1 item on right, regardless of how many commas in string:

select distinct         students.student_name ,         students.grade_id ,         books.book_title ,         books.price    ( books           inner join courses on books.course_id = courses.course_id         )         inner join students on courses.grade_id = students.grade_id   ( students.id ) = 3         , cstr(books.course_id) in ( '9,1,2' ); 

when trying say, can't in way,

select distinct         students.student_name ,         students.grade_id ,         books.book_title ,         books.price    ( books           inner join courses on books.course_id = courses.course_id         )         inner join students on courses.grade_id = students.grade_id   ( students.id ) = 3         , cstr(books.course_id) in ( '9','1', '2' ); 

there 2 solutions can think of.

the first , better approach not use comma delimited field, create linking table (aka many many relationship) between student table , courses table. linking table contains keys of students , courses student has access to. join students, linking table, , linking table courses.

the second, , not (also dangerous) possibility create variable contains sql statement , executes it. can very, problematic if can put kind of data in string column, , recommend not doing that.

design database properly, appropriately normalized data.