"Lukas Haase" <lukasha...@gmx.at> wrote in message news:gv9fcm$5r...@ger.gmane.org > I have a database containing thousands of HTML pages ("topics"). There > is a fulltext index for these topics. First there is a table > containing all single words. Each word is identified by its > "fulltextID": > > CREATE TABLE fulltext( > fulltextID INTEGER PRIMARY KEY, > word VARCHAR(100) COLLATE NOCASE > ); > > Now there is a linking table between the words and the HTML pages > (topics): > > CREATE TABLE topic_fulltext( > topicID INTEGER, > fulltextID INTEGER, > PRIMARY KEY(topicID, fulltextID) > ); > > Finding a topic containing a specific word is not too hard: > > SELECT topic_fulltext.topicID > FROM fulltext > JOIN topic_fulltext ON topic_fulltext.fulltextID = fulltext.fulltextID > WHERE word LIKE 'Word%'; > > But now I want to be able to search with more complex queries. For > example: > > * List all topics containing (word1 AND word2)
You could do something like this: SELECT topic_fulltext.topicID FROM topic_fulltext where exists (select 1 from fulltext WHERE topic_fulltext.fulltextID = fulltext.fulltextID and word LIKE 'word1%') and exists (select 1 from fulltext WHERE topic_fulltext.fulltextID = fulltext.fulltextID and word LIKE 'word2%') -- or SELECT topic_fulltext.topicID FROM topic_fulltext where fulltextID in ( select fulltextID from topic_fulltext where word LIKE 'word1%' intersect select fulltextID from topic_fulltext where word LIKE 'word2%'); Test it, see which one works faster. > * List all topics containing (word1 OR word2) Similar to above, but replace AND with OR, and INTERSECT with UNION ALL. > * List all topics containing (word1 AND word2 AND ... AND word10) > * List all topics containing ((word1 OR word2) AND word3 OR word3) The approach above should work for any boolean combination. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users