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
