Android Question sqlite how to filter from a list

alain bertrand

Member
Licensed User
From a first Query in Table1, I've extracted a list_of_IDs.

I'd like to Query again in Table2 according to this list. Something like:
SELECT Table2.Col1, Table2.Col2 ... FROM Table2 WHERE Table2.rowid IN list_of_IDs ORDER BY ...

SQLite v1.50: syntax error. IN seems not supported.
Is it a way around ?
 

DonManfred

Expert
Licensed User
SELECT Table2.Col1, Table2.Col2 ... FROM Table2 WHERE Table2.rowid IN list_of_IDs ORDER BY ...
try
B4X:
SELECT Table2.Col1, Table2.Col2 ... FROM Table2 WHERE Table2.rowid IN (list_of_IDs) ORDER BY ...
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
From a first Query in Table1, I've extracted a list_of_IDs.

I'd like to Query again in Table2 according to this list. Something like:
SELECT Table2.Col1, Table2.Col2 ... FROM Table2 WHERE Table2.rowid IN list_of_IDs ORDER BY ...

SQLite v1.50: syntax error. IN seems not supported.
Is it a way around ?

B4X:
select col1, col2 from table2 where rowid in(select id from table1 where colX = 'abc') order by ...

Not sure there is a logical correlation between the rowid of table2 and the id of table1.
You will need to post the schema of both tables and explain how they relate.

RBS
 
Upvote 0

alain bertrand

Member
Licensed User
Thanks! Both answers are working.
1. IN requires a code constructed STRING (not a list) eg "(id0,id1,id2...)" without trailing coma "(id0,id1,id2,)".
2.
B4X:
SELECT col1, col2 FROM table2 WHERE rowid IN (SELECT DISTINCT id FROM table1 WHERE colx = 'abc')
is more DB minded.
 
Upvote 0
Top