On 23 May 2009, at 7:30pm, Lukas Haase wrote: > 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) > * List all topics containing (word1 OR word2)
It works perfectly to do things like WHERE (word LIKE 'Word1%') AND (word LIKE 'Word2%') WHERE (word LIKE 'Word1%') OR (word LIKE 'Word2%') But chaining too many of these together might make for slow searches. I don't know how good sqlite is at doing this stuff. > * List all topics containing (word1 AND word2 AND ... AND word10) > * List all topics containing ((word1 OR word2) AND word3 OR word3) You could write something to transform those into the format I showed above. And you could get the results directly using SELECT or use CREATE VIEW to reflect them. But there are other ways to do it that might be more efficient (i.e. faster) or simpler to program and debug. For instance, if you have a chain of conditions you could CREATE TEMPORARY a table to then use it to accumulate (OR) or eliminate (AND) the pages you want using the form of INSERT that takes a SELECT argument. Depending on the size and shape of your database this may or may not be faster. What you've hit is the standard problem of flexible search fields and doing it well is one reason Google is the success it is. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
