Thanks for the help Scott, you've confirmed what I had concluded. Thanks again
M -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Hess Sent: 20 May 2008 17:00 To: General Discussion of SQLite Database Subject: Re: [sqlite] FTS3 Question I think you're going to have to run some code to generate the string to match against. The problem is that you need to take all of the 'query' fields from 'category' and combine them into a string like 'query1 OR query2 OR query3 OR ...'. I'm not aware of a way to do this with straight SQL. You could perhaps build an aggregate function which took strings and combined them, then it might be something like: SELECT guid FROM data WHERE text MATCH (SELECT string_join(query, ' OR ') FROM category); Otherwise, just do the join in your application code and feed it back to the match. -scott [BTW, 'query' and 'text' are probably not strong column names, being part of SQL syntax already.] On Mon, May 19, 2008 at 11:36 PM, Mike Marshall <[EMAIL PROTECTED]> wrote: > What I'm trying to do is get the query strings that are stored in category > executed against the text stored in data. Category is essentially a fixed > set of content, whilst data changes. I could just step through category and > execute each query individually, but I was looking for a way to do it in a > single operation. > > Basically ' which contain _any_ of the 'query' items from 'category'? ' > > > > -----Original Message----- > On Behalf Of Scott Hess > > Should the 'data' table be joinable with the 'category' table in some > way? Are you trying to match rows in 'data' which contain _all_ of > the 'query' items from 'category', or which contain _any_ of the > 'query' items from 'category'? Do you mean to have a WHERE clause or > anything on what you're pulling in from 'category'? > > As presented, you've got "I do this, it doesn't work", which I can > agree with. But I can't quite figure out what your intention for > "works" is :-). > > -scott > > > > On Sat, May 17, 2008 at 12:49 AM, Mike Marshall > <[EMAIL PROTECTED]> wrote: >> I have an FTS3 table created as follows >> >> >> >> CREATE VIRTUAL TABLE data USING fts3(guid, text) >> >> >> >> And a standard table created thus >> >> >> >> CREATE TABLE category (label, query) >> >> >> >> >> >> What I would like to be able to do is an SQL query of the form >> >> >> >> SELECT guid FROM data WHERE text MATCH SELECT query FROM category >> >> >> >> But I can't seem to get it to work. >> >> >> >> Should it work? And if it should can someone point out what I am doing >> wrong. >> >> >> >> Thanks >> >> >> >> Mike >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
