On 12-11-2011 12:12, Alexandre Courbot wrote: > Hi Igor, > > On Nov 12, 2011 11:45 AM, "Igor Tandetnik" <itandet...@mvps.org> wrote: >> This query doesn't make much sense. It appears that quite a few > conditions are redundant, or else the parentheses are in the wrong places. > What logic were you trying to express here? > > Sorry, I tried to simplify it as much as possible but this is right is does > not make much sense. I have two FTS tables containing strings for > definitions, one is the definition in English, the other is the same > definition in another language. Both tables use the same rowid for the same > definition, but while every definition has an entry in the English table, > some are missing in the non-English one. > > I want to find out which entries have a given word in their definition. So > the subqueries are actually matches against the FTS table. However, I do > not want an entry to match in case the match is made against the English > FTS table, and a non-matching word also exists in the non-English table. > That is, the English table should only be used for entries which do not > have a non-English definition. > > Put it differently, an entry should match if: > - the looked up word is matches in the non-English table, OR > - the non-English table does not have a row for the entry, AND the > corresponding English definition matches. > > I cannot reasonnably use joins here because I would have to use left joins > (because of the OR condition) and observed severe performance degradation > if I do so. This is because the FTS tables' rowid does not directly > correspond to an entry ID, but must be matched with another table to get > the entry id. > > I hope this is clear enough - sorry for the long explanation. I have > possible workarounds to make this work differently, but being able to > reference the subqueries directly would be the cleanest to me. > > Thanks, > Alex.
Could you post a small example of some real-life data? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users