Hi, At first, thank you for your answer!
Simon Slavin schrieb: > 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%') No, this is unfortunately not the case :-( I know the usage of LIKE, % and =. But the problem is here that SELECT topic_fulltext.topicID FROM fulltext JOIN topic_fulltext ON topic_fulltext.fulltextID = fulltext.fulltextID WHERE (word LIKE 'Word1%') AND (word LIKE 'Word2%'); would give me no results. And the one statement with WHERE (word LIKE 'Word1%') OR (word LIKE 'Word2%'); would work... And, I have forgotten some other search cirteria: * List all topics containing (word1 AND NOT word2) > [...] >> * 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. This indeed no problem but the query does not work. The problem is that I have a list of words and then a table which topic contains which words. So if I want to know which *topics* are linked with * word1 OR word2 * word1 AND word2 * word1 AND NOT word2 I need a completely different query. > 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. Yes, the DB is very huge. There are 20k topics and the fulltext and topic_fulltext tables are approx. 50MB. > [...] > Simon. Luke _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
