On 24 May 2009, at 1:28pm, Lukas Haase wrote: > 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%'; >> >> 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.
Oh, I get it. You have indexed your documents and have a many-to-many relationship which is something like column 1 = word column 2 = document number So one word appears in many documents and one document contains many words. That makes sense. In the case it looks like the best way is to build up a TEMPORARY table something like column 1 = document using INSERT OR IGNORE, parsing your search requirements (AND, OR, NOT, etc.) yourself and turning them into successive INSERT and DELETE commands. So an 'OR' relationship would turn into two INSERT commands, whereas an 'AND' relationship would be an INSERT and then a DELETE for all cases where the document didn't contain the second word. If you want to allow bracketing in your search strings the programming can be complicated. If you don't, and just work from the left to the right of your search strings it should be simple. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
