Subject Re: Max length of SQL statement
Author Stephen Boyd
I finally figured it out.

If I have 2 tables:

CREATE TABLE DOCUMENTS (
DOC_NUM INTEGER NOT NULL,
...,
PRIMARY KEY (DOC_NUM) );

and

CREATE TABLE KEYWORDS (
KEYWORD VARCHAR(50) NOT NULL,
DOC_NUM INTEGER NOT NULL,
PRIMARY KEY (KEYWORD, DOC_NUM) );
CREATE INDEX DOC_KEY ON KEYWORDS (DOC_NUM, KEYWORD);

If I want all documents containing any of the specified keywords (KEY1
or KEY2) I do this:

SELECT Z.*
FROM KEYWORDS A
LEFT JOIN DOCUMENTS Z ON Z.DOC_NUM = A.DOC_NUM
WHERE A.KEYWORD = 'KEY1' OR
A.KEYWORD = 'KEY2' ...

Simple and obvious. If I want all documents containing all of the
specified keywords (KEY1 and KEY2) I do this:

SELECT Z.*
FROM KEYWORDS A
INNER JOIN KEYWORDS B ON B.DOC_NUM = A.DOC_NUM AND
B.KEYWORD = 'KEY1'
INNER JOIN KEYWORDS C ON C.DOC_NUM = A.DOC_NUM AND
B.KEYWORD = 'KEY2'
LEFT JOIN DOCUMENTS Z ON Z.DOC_NUM = A.DOC_NUM
WHERE A.KEYWORD = 'KEY1' OR
A.KEYWORD = 'KEY2' ...

Not nearly so obvious, but this gives me the result I need without
complete table scans and it is reasonably fast.