How do you match multiple text in multiple column in a table with SQLite FTS3?

Question

I'm searching for Multiple text in multiple column of Virtual Table. I have checked this thread, this search for a single word in multiple column.

I checked with following

SELECT * FROM table WHERE table MATCH (('A:cat OR C:cat') AND ('A:dog OR C:dog')

but it seems AND condition not working.

EDIT I have tried with following,

Select count (*) FROM Table1 WHERE TBL_VIRTUAL MATCH (('A:D* AND B:D* AND C:D*') OR ('A:tar* AND B:tar* AND C:tar*'));
Select count (*) FROM Table1 WHERE TBL_VIRTUAL MATCH (('A:D* AND B:D* AND C:D*') AND ('A:tar* AND B:tar* AND C:tar*'));

These both query return me same 109 result. Then I tried what @redneb mention in below answer:

SELECT * FROM table WHERE table MATCH '(A:D* OR B:D* OR C: D*) AND (A:tar* OR B:tar* OR C:tar*)'
SELECT * FROM table WHERE table MATCH '(A:D* OR B:D* OR C: D*) OR (A:tar* OR B:tar* OR C:tar*)'

But this return 0 result.

Any suggestion what I'm missing here!!

Answer 1

Try this instead:

SELECT *
FROM mytable
WHERE mytable MATCH '(A:cat OR C:cat) AND (A:dog OR C:dog)';

However, I suspect that the following query will perform faster:

SELECT *
FROM mytable
WHERE mytable MATCH '(A:cat AND C:dog) OR (A:dog AND C:cat)';

and is equivalent to the first one.

Edit: Here's a complete example. Let's create and populate a table first:

CREATE VIRTUAL TABLE mytable USING fts3(A, C);
INSERT INTO mytable VALUES
    ('foo','bar'),
    ('dog','dog'),
    ('cat','cat'),
    ('dog','cat'),
    ('cat','dog');

Then the query works as expected:

sqlite> SELECT * FROM mytable WHERE mytable MATCH '(A:cat AND C:dog) OR (A:dog AND C:cat)';
A           C         
----------  ----------
dog         cat       
cat         dog       

Answer 2

For OR condition type OR between i.e. : MATCH ('A:cat OR C:cat')

For AND condition just don't type anything i.e. : MATCH ('A:cat C:cat')