Lost 'in' SQL - need help with IN function

[Evan]

Hi guys, I have a database table (documents) with a structure that
looks like this:
docID docName keyWords
1 testTitle01.doc dog,cat,bob
2 testTitle02.doc hello,world
3 testTitle03.doc please,help,me
4 testTitle04.doc is,that,a,dog
5 testTitle05.doc it,wasnt,me
6 testTitle06.doc you,are,funny
7 testTitle07.doc as,a,clown

I want to write a SQL query that accepts a users input and searches
the keyword fields. I am not sure how to do this because the user
input will be in the format of: The,cat,ate,the,mouse and it will be
assinged to a form field called keyWordInput.

If there is an easy way to do this, I would greatly appreciate any
assistance.

Regards,

Evan

[David Portas]

You should drop the "Keywords" column and put the keywords in a separate
table. Non-atomic columns are a violation of basic design principles and
your problem problem is much easier to solve if you fix the design first.
Assuming you create a table like this:

CREATE TABLE DocumentKeywords (docid INTEGER NOT NULL REFERENCES Documents
(docid), keyword VARCHAR(30) NOT NULL, PRIMARY KEY (keyword,docid))

Your client application can then pass the keywords to a query like this:

SELECT DISTINCT D.docid, D.docname
FROM Documents AS D,DocumentKeywords AS K
WHERE D.docid = K.docid
AND K.keyword IN (@keyword1,@keyword2,@keyword3, ...)

Alternatively, use another table populated with your keyword search
criteria:

CREATE TABLE KeyWordSearch (session_id INTEGER NOT NULL, keyword VARCHAR(30)
NOT NULL, PRIMARY KEY (keyword, session_id))

SELECT DISTINCT D.docid, D.docname
FROM Documents AS D,DocumentKeywords AS K, KeyWordSearch AS S
WHERE D.docid = K.docid
AND K.keyword = S.keyword
AND S.session_id = @session_id

--
David Portas
SQL Server MVP
--