"Lukas Haase" <[email protected]> wrote in
message news:[email protected]
>> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
