Sqlite FTS, Using OR between match operators

Question

When I execute the following query in a sqlite engine (android or sqlitebrowser) it throws an exception that says unable to use function MATCH in the requested context.

select
    a.Body1,
    b.Body2
from
    tbl1_fts  as a,
    tbl2_fts  as b
where
    a.ID = b.ParentID and

    (
        a.Body1 match('value') or
        b.Body2 match('value')
    )

-Both tables have fts.
-Using And operator between two matches (instead of OR) runs normally.

How can I fix this or change the query to find rows with above condition?

Error

Answer 1

you can not use OR Operation, just change your Match Keyword. like SELECT * FROM docs WHERE docs MATCH 'sqlite OR database';

OR maybe you can use union

SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database' UNION SELECT docid FROM docs WHERE docs MATCH 'library';

Garniture

Answer 2

MATCH as a function would have two parameters:

... WHERE match('value', SomeColumn) ...

However, the usual method of using MATCH is as an operator:

... WHERE SomeColumn MATCH 'value' ...

Pneumograph

Answer 3

This SQL worked for me in FTS5

select 
  *
from 
  "card" 
  inner join "note" on "card"."noteId" = "note"."id" 
  inner join "noteFtsTag" on "noteFtsTag"."rowid" = "note"."rowid" 
  inner join "cardFtsTag" on "cardFtsTag"."rowid" = "card"."rowid" 
where 
  (
    "noteFtsTag"."rowid" in (
      select 
        "rowid" 
      from 
        "noteFtsTag" 
      where 
        "noteFtsTag"."tags" match ?
    ) 
    or "cardFtsTag"."rowid" in (
      select 
        "rowid" 
      from 
        "cardFtsTag" 
      where 
        "cardFtsTag"."tags" match ?
    )
  )

Should also work in FTS3; source: https://sqlite.org/forum/forumpost?udc=1&name=1a2f2ffdd80cf795

Australoid

Answer 4

MATCH has to be used without parentheses.

The AND and OR operators must be in capital letters when used with FTS.

Dittman