Is it possible to MATCH across JOINed FTS4 tables?

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Is it possible to MATCH across JOINed FTS4 tables?

Dave Waters
I can't quite figure out the right method to perform a MATCH query across
joined FTS tables.  Does anyone know of a method where this is possible?

As an example, I have two FTS4 tables:

CREATE VIRTUAL TABLE person USING
fts4(personid,firstname,lastname,addressid)
CREATE VIRTUAL TABLE address USING fts4(addressid,street,city,state,zip)

This results in giving me all people named 'bob' and their address:
SELECT * FROM people INNER JOIN household ON people.phh = household.hhid
WHERE people MATCH 'bob'

But if I want to query all people named 'bob' in 'Chicago':
SELECT * FROM people INNER JOIN household ON people.phh = household.hhid
WHERE people MATCH 'bob chicago'

I understand why this fails (I am MATCHing on the people table), but I am
uncertain if there is another way to do this, besides de-normalizing these
two tables into one.

This is just an example.  For my task, I need to use FTS, and MATCH.

Thanks for any suggestions.
--
Dave
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users