WHERE clause from another table using NOT IN

0
votes

I am unable to run a query in sqlite android, it is throwing below error:

android.database.sqlite.SQLiteException: no such table: table1.item1 (code 1): ,while compiling: SELECT * from table1, table2 where table1.item1 not in table2.item1

my raw query is:

Database.rawQuery("SELECT * from "+ TABLE1 +", "
    +TABLE2+" where "+ TABLE1 +"" + "."+ ITEM1 +" not in "
            + TABLE2 +"."+ ITEM1, null);

String constants:

TABLE1= "table1", TABLE2="table2", ITEM1= "item1"

I want all the rows in table1 whose item1 is not in the list of item1 values in table2.

Do I need to add some GROUP BY statement?

1
androidsqlsqlitewherenotin

1 Answers

4
votes

If that is what you are looking for, this would be your query:

SELECT * FROM table1 WHERE item1 NOT IN (SELECT item1 FROM table2)

Your "SELECT * FROM table1, table2" would give you a combination of every returned record of table1 and table2. It's not often that is intended.