On Sat, 29 Feb 2020 at 00:45, Keith Medcalf <kmedc...@dessus.com> wrote:
> > In other words, why would one want to do: > > select * from data where uuid in (select uuid from data where twit == 1 > INTERSECT select uuid from data where twat == 1 INTERSECT select uuid from > data where lastname like 'cricket%' INTERSECT select uuid from data where > not firstname like 'jimmy%') > > when one could simply forgo all the crap and do it directly: > > select * from data where (twit == 1) and (twat == 1) and (lastname like > 'cricket%') and (not firstname like 'jimmy%'); Good question, and one that I should have thought to answer pre-emptively: 1. The results of these queries are displayed to a user in an app in tabular format. The user can select filters and change sort order independently. The sort table (actually a view) has unique uuids whereas the filter table can have multiple entries for each uuid. So it made sense to me to build a temporary table with the unique uuid results of filtering, then JOIN that table with the sort table for presentation. That way, the sort order can change without having to re-query the filters. I may be over-thinking this. 2. This is for a mobile app and I’m using the C API. So I’ve been trying to use prepared queries. There can be any number of LIKE filters applied (including none) and I figured it would be more expensive to build and parse a new statement each time than to apply a pre-compiled one several times. Again, I may be making incorrect assumptions. I suppose what I’m trying to do is essentially: First filter: INSERT INTO TemporaryTable (uuid) SELECT DISTINCT uuid FROM FilterData WHERE filter LIKE ? Subsequent filters: DELETE FROM TemporaryTable WHERE uuid NOT IN (SELECT DISTINCT uuid FROM FilterData WHERE filter LIKE ?) The common case is 0 filters, but 1 or 2 filters is not uncommon. And I should mention that at some point I’m going to be adding FTS, in case this changes matters! Are my assumptions faulty? All advice gratefully received. Thanks, Hamish > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
