Re: [sqlite] select where value in

2010-11-18 Thread Simon Slavin

On 18 Nov 2010, at 1:22pm, Joseph Garry wrote:

>> From: itandet...@mvps.org
>> Joseph Garry  wrote:
>>> I'd like to set up a compiled sqllite (sqlite3_prepare_v2) statement with a 
>>> query like
>>> 'select tabid, col1, col2 from table where tabid in (?)'
>>> But how do I bind in the parameter here? An example would be welcome, of 
>>> course.
>> 
>> You can't do that directly. One way is to create a temp table, like this:
>> 
>> create temp table TabIdList (tabid integer);
>> 
>> Now you can prepare this statement:
>> 
>> select tabid, col1, col2 from myTable where tabid in (select tabid from 
>> TabIdList);
>> 
>> Populate TabIdList with your list of IDs (you can use a prepared statement 
>> for this, too, along the lines of "insert into TabIdList(tabid) values (?); 
>> ").

> That would work, but the thing I'm after here is speed. And I can't imagine 
> what you're suggesting would be very fast. Am I mistaken?

Put an index on the 'tabid' field of myTable and it'll be pretty fast.

(PS: I fixed the text order of your reply.)

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] select where value in

2010-11-18 Thread Joseph Garry

That would work, but the thing I'm after here is speed. And I can't imagine 
what you're suggesting would be very fast. Am I mistaken?
 
> To: sqlite-users@sqlite.org
> From: itandet...@mvps.org
> Date: Thu, 18 Nov 2010 08:08:51 -0500
> Subject: Re: [sqlite] select where value in
> 
> Joseph Garry <josephga...@hotmail.com> wrote:
> > I'd like to set up a compiled sqllite (sqlite3_prepare_v2) statement with a 
> > query like
> > 'select tabid, col1, col2 from table where tabid in (?)'
> > But how do I bind in the parameter here? An example would be welcome, of 
> > course.
> 
> You can't do that directly. One way is to create a temp table, like this:
> 
> create temp table TabIdList (tabid integer);
> 
> Now you can prepare this statement:
> 
> select tabid, col1, col2 from myTable where tabid in (select tabid from 
> TabIdList);
> 
> Populate TabIdList with your list of IDs (you can use a prepared statement 
> for this, too, along the lines of "insert into TabIdList(tabid) values (?); 
> ").
> -- 
> Igor Tandetnik
> 
> ___
> 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


Re: [sqlite] select where value in

2010-11-18 Thread Igor Tandetnik
Joseph Garry  wrote:
> I'd like to set up a compiled sqllite (sqlite3_prepare_v2) statement with a 
> query like
> 'select tabid, col1, col2 from table where tabid in (?)'
> But how do I bind in the parameter here? An example would be welcome, of 
> course.

You can't do that directly. One way is to create a temp table, like this:

create temp table TabIdList (tabid integer);

Now you can prepare this statement:

select tabid, col1, col2 from myTable where tabid in (select tabid from 
TabIdList);

Populate TabIdList with your list of IDs (you can use a prepared statement for 
this, too, along the lines of "insert into TabIdList(tabid) values (?); ").
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] select where value in

2010-11-18 Thread Joseph Garry

I'd like to set up a compiled sqllite (sqlite3_prepare_v2) statement with a 
query like
'select tabid, col1, col2 from table where tabid in (?)'
But how do I bind in the parameter here? An example would be welcome, of 
course.  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users