"Lukas Haase" <lukasha...@gmx.at> wrote in message news:gve1lh$30...@ger.gmane.org >> 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%') > > Unfortunately this does not work completely :( > > In the first WHERE clause I restrict to entried containing only the > "word1" (the resultset will contain only entries with "word1"). So the > second WHERE clause will always fail as there are no rows with > "word2" left.
Right. I was thinking about a third table, topics, that lists all topics (and likely additional information about them), so that you have a classic many-to-many relationship. I suspect you have one. In this case you can do SELECT topics.topicID FROM topics where exists (select 1 from topic_fulltext join fulltext on (topic_fulltext.fulltextID = fulltext.fulltextID) WHERE topic_fulltext.topicID = topics.topicID and word LIKE 'word1%') and exists (select 1 from topic_fulltext join fulltext on (topic_fulltext.fulltextID = fulltext.fulltextID) WHERE topic_fulltext.topicID = topics.topicID and word LIKE 'word2%'); If for some strange reason you don't have topics table, then you can do SELECT distinct tf1.topicID from topic_fulltext tf1 where exists (select 1 from topic_fulltext tf2 join fulltext on (tf2.fulltextID = fulltext.fulltextID) WHERE tf1.topicID = tf2.topicID and word LIKE 'word1%') and exists (select 1 from topic_fulltext tf2 join fulltext on (tf2.fulltextID = fulltext.fulltextID) WHERE tf1.topicID = tf2.topicID and word LIKE 'word2%') Basically, (select distinct topicID from topic_fulltext) plays the role of topics table. >> 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%'); > > Unfortunately this does not work either. For the same reason. Make it select topicID from topic_fulltext join fulltext on ( topic_fulltext.fulltextID=fulltext.fulltextID) where word LIKE 'word1%' intersect select topicID from topic_fulltext join fulltext on ( topic_fulltext.fulltextID=fulltext.fulltextID) where word LIKE 'word2%' Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users