Hi Igor,
Thanks for your reply.
On Wed, Jan 14, 2009 at 4:16 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
> Sam Thursfield <sss...@gmail.com> wrote:
>> I have a query such as this:
>>
>> SELECT * FROM local INNER JOIN foreign ON local.foreign_id =
>> foreign.id WHERE foreign.id IN (4, 3, 6, 7) ORDER BY local.name;
>>
>> Here the list of numbers in the WHERE clause is subject to change. How
>> possible do you think it is to have this as a prepared query?
>>
>> SELECT * ... WHERE foreign.id IN ($list) ...;
>
> The usual solution is to create a temp table, populate it with your list
> (using a prepared INSERT statement), then in SELECT statement write
> something like
>
> WHERE foreign.id IN (SELECT id FROM temp.ids)
>
>> - binding text results in SELECT * ... WHERE foreign.id IN ("4. 3, 6,
>> 7") ...; which of course doesn't work.
Do you know off hand whether this method would be much faster than
compiling a new query for each new list of ids? If not I think I'll do
some quick research.
> But something like this would work, if you want to go this way:
>
> WHERE ',4,3,6,7,' LIKE '%,' || foreign.id || ',%'
>
> Though if you are prepared to engage in string manipulation like this,
> you could just as well embed the list into the query string and not
> bother with parameters.
Yeah this is an interesting solution but I figure just making a new
query with printf and preparing it each time would be quicker :)
Sam
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
