I am learning how to use FTS4 in SQlite and trying to translate something like this:
SELECT * FROM table
WHERE (postal_code LIKE q1 OR place_name LIKE q1) AND (address LIKE q2)
which works perfectly with the normal table, to something like that:
SELECT * FROM table
WHERE table MATCH '(postal_code: q1 OR place_name: q1) AND (address: q2)'
which is not working with the same table converted to VIRTUAL .. USING fts4...
Searching for match in specific column with ":" is working, but how can I combine this with OR and AND?
Background - I try to work with a flexible query, where q1 can be one of the both: postal-code or place name and q2 has to be address.
EDIT:
This is what I got so far (partially) working
SELECT * FROM table WHERE table MATCH ('postal_code:q1 OR place_name:q1')
INTERSECT
SELECT * FROM table WHERE table MATCH ('address:q2');
(building on this post from @CL)
The first problem here are words with space - if I search for something like
...MATCH ('postal_code:"new haven*" OR place_name:"new_haven*"')
then I have "malformed MATCH expression" The second problem is when I have empty query, like
...MATCH ('*')
then I get nothing