> "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
