Comma Separated columns - Search

If you're sking SQL questions, you'd better off asking on a SQL forum.  But here you are, so let's have a look at it.

Firstly, the other thing to always mention when raising an SQL question is to articulate what DB system you're using.  Because all of them handle SQL differently, and have their own extensions.

In fact reading this is probably a good idea: http://www.catb.org/~esr/faqs/smart-questions.html

Problem_code -------------------- 1 2,3 5,6,7,8 5 ---------- And the list values I am sending as ("1","3")...etc SELECT * from table where problem_code in ('2','1') problem_code in ('2','1') Thank you in advance..

What are you seeing here that's not expected (I presume the double-up of problem_code in ('2','1') is a typo)?

Are you wanting to get any rows from TABLE which have either 1 or 2 in them?  That's not what you're asking for in your WHERE statement though, is it?

Really, you should be normalising your data, because whilst you can write contorted logic to do what you need here, it will not perform well and certainly will not scale.  So it for be somewhat poor practice to perpetuate the current situation if it's at all possible not to.   The best thing to do is to normalise your data and refactor your code.  Which will probably mean going to your boss and say "this DB schema is f*cked, and needs reworking.  Needs reworking".

Depending on your DB system, you're probably going to need to loop over the list you want to filter on and do a "WHERE problem_code LIKE" on each element of the list.  I think Oracle can do the sort of query you want to do - match elements of one list from another list - but I'm not sure.

--

Adam

The biggest problem is a poorly designed database.  If possible, normalize it.  If you don't know how, I've heard good things about the book, Database Design for Mere Mortals.