Re: [sqlite] Results even if no rows

2006-07-13 Thread andrew gatt 
> "andrew gatt" <[EMAIL PROTECTED]> writes:
>
> > I'm trying to come up with an SQL statement that works under sqlite that
can
> > return a list of rows depending if a list of ids exist.  For individual
> > checking i'm using:
> >
> > select 1 from table where id == 1;
> >
> > which returns a 1 if there is a row or no rows if it doesn't exist.
However
> > i have a large list to check and was trying to think of a way to
optimise
> > this. I can use:
> >
> > select 1 from table where id == 1 OR id == 2;
> >
> > which will return:
> >
> > 1
> > 1
> >
> > if they both exist, but if one doesn't exist i can't tell which one it
is. Is there some way to get the statement to return a row with a 0 if the
id doe not exist?
> >
> > 1
> > 0
> >
> > Or do i have to do the check for each one?
>
> How about this:
>
> SQLite version 3.2.1
> Enter ".help" for instructions
> sqlite> create table x (id integer);
> sqlite> insert into x values (1);
> sqlite> insert into x values (3);
> sqlite> select (id in (1,2)) from x;
> 1
> 0
> sqlite> select (id in (1,2,3)) from x;
> 1
> 1
> sqlite>

This is very close, however it returns a result for every row that exists in
the table.

sqlite> create table x (id integer);
sqlite> insert into x values (1);
sqlite> insert into x values (3);
sqlite> insert into x values (9);
sqlite> insert into x values (56);
sqlite> select (id in (1,2)) from x;
1
0
0
0
sqlite> select (id in (1,2,3)) from x;
1
1
0
0
sqlite>

so i still don't really know which ids exist and which don't?
I'm not sure how but if it could return a row per id in the brackets it
would be perfect.

Andrew

Re: [sqlite] Results even if no rows

2006-07-13 Thread Derrell . Lipman 
"andrew gatt" <[EMAIL PROTECTED]> writes:

> I'm trying to come up with an SQL statement that works under sqlite that can
> return a list of rows depending if a list of ids exist.  For individual
> checking i'm using:
>
> select 1 from table where id == 1;
>
> which returns a 1 if there is a row or no rows if it doesn't exist. However
> i have a large list to check and was trying to think of a way to optimise
> this. I can use:
>
> select 1 from table where id == 1 OR id == 2;
>
> which will return:
>
> 1
> 1
>
> if they both exist, but if one doesn't exist i can't tell which one it is. Is 
> there some way to get the statement to return a row with a 0 if the id doe 
> not exist? 
>
> 1
> 0
>
> Or do i have to do the check for each one?

How about this:

SQLite version 3.2.1
Enter ".help" for instructions
sqlite> create table x (id integer);
sqlite> insert into x values (1);
sqlite> insert into x values (3);
sqlite> select (id in (1,2)) from x;
1
0
sqlite> select (id in (1,2,3)) from x;
1
1
sqlite>

[sqlite] Results even if no rows

2006-07-13 Thread andrew gatt 
Hi all,

I'm trying to come up with an SQL statement that works under sqlite that can 
return a list of rows depending if a list of ids exist.
For individual checking i'm using:

select 1 from table where id == 1;

which returns a 1 if there is a row or no rows if it doesn't exist. However i 
have a large list to check and was trying to think of a way to optimise this. I 
can use:

select 1 from table where id == 1 OR id == 2;

which will return:

1
1

if they both exist, but if one doesn't exist i can't tell which one it is. Is 
there some way to get the statement to return a row with a 0 if the id doe not 
exist? 

1
0

Or do i have to do the check for each one?

Thanks for your help

Andrew