Hello All, (my first post here)
I have various tables which all relate to the same central object, all of them contain various pieces of information about this object. And I want to full-text-search for these objects. I.e. in my case sounds (which have tags, comments, metadata, categories, ratings, etc etc). I wanted to use FTS2 to search through sounds, in the end I settled for a trigger approach: adding a new tag for example will trigger an update of the search-table. create virtual table search using fts2(sound_id, content); create trigger if not exists soundtag_insert after insert on tag for each row begin update search set content=(....) where search.sound_id=new.sound_id; end; etcetera for all my tables related to the sound. All well, but then I tried: select sound.* from sound left join search on sound.id=search.sound_id where search.content match "bass drum" and sound.samplerate=44100; and got: SQL error: unable to use function MATCH in the requested context The solution I use now is a subselect: select * from sound where samplerate=44100 and id in (select sound_id from search where content match "bass drum"); but this will get me into trouble for large datasets - I suppose. Is there a better solution for this problem or not? I also know FTS2 uses only text-data, so I suppose my join will be slower (or am I assuming too much) than a join between two tables with integer keys? thanks a lot, - bram _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users