Of course, you could just as well do: select * from data where (...) AND (...) AND (...) <repeat until finished>;
and do away with all the extraneous stuff. 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%'); -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: Keith Medcalf <kmedc...@dessus.com> >Sent: Friday, 28 February, 2020 17:37 >To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> >Subject: RE: [sqlite] Intersecting multiple queries > > >select stuff from data where uuid in (select uuid from data where ... >INTERSECT select uuid from data where ... INTERSECT select uuid from data >where ... <repeat until finished>); > >-- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > >>-----Original Message----- >>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >>Behalf Of Hamish Allan >>Sent: Friday, 28 February, 2020 16:02 >>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >>Subject: [sqlite] Intersecting multiple queries >> >>Hi, >> >>I am building a list of UUIDs from multiple queries of the form: >> >>SELECT uuid FROM Data WHERE filter LIKE ? >> >>with a different bound parameter each time. >> >>In app-space code, I'm getting the results of these queries and >>intersecting them, so that the final list contains only UUIDs returned >by >>all of the queries. >> >>I'm then populating a temporary table so that a further query can return >>only rows matching those UUIDs. >> >>What I'm wondering is if there's a shortcut to avoid having to build the >>UUID list in app code -- whether I can perform an intersection in a >query >>to build that temporary table without involving app-space code. Or even >>avoid having to build the temporary table at all? >> >>Many thanks, >>Hamish >>_______________________________________________ >>sqlite-users mailing list >>sqlite-users@mailinglists.sqlite.org >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
