On Thu, May 22, 2008 at 1:41 PM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote:
> Hello All, > For example we have table like this: > > CREATE TABLE map ( > name text, > x integer, > y integer > ); > CREATE INDEX map_xy ON map(x,y); > > How to query this table with "IN" keyword? > Query like this, doesn't work: > > SELECT * FROM map WHERE (x,y) IN ((1,1),(1,2),(1,3)); select * from map where x=1 and y in (1,2,3); 1. Some people might suggest some crazy things like [ SELECT * FROM map WHERE x||'.'||y in ('1.1','1.2','1.3') ]. While this would technically work, it wouldn't be able to use your index. 2. I tried this first: [ select * from map where (x=1 and y=1) or (x=1 and y=2) or (x=1 and y=3) ] but that didn't use the index -- not on 3.5.6 anyway 3. If you're going to have a bunch of choices with different values for X and Y, you *might* want to creating a precomputed statement of the form 'select * from map where x=? and y=?', then binding and re-executing the statement for each (x,y) pair you're interested in, and piece them together in your application. You wouldn't be able to take advantage of ORDER BY, GROUP BY, or DISTINCT that way, but it would work. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users