Java – Android SQLite uses the WHERE clause to return no results

I’m running the sqlite query below and I know it’s not the best approach, but I just want it to work right now.

If I run these two queries, I get the results.

                Cursor value = checkDB.rawQuery("SELECT * FROM table WHERE open ='1'", null);
                Cursor value = checkDB.rawQuery("SELECT * FROM table WHERE country='"+ countryID +"'", null);

But if I combine these two where clauses, I won’t get any results. Am I looking too simple?

                    Cursor value = checkDB.rawQuery("SELECT * FROM table WHERE open ='1' AND country='"+ countryID +"'", null);

As it turns out, I should consider this to be relevant in an extensible ListView. Here’s the complete code for a nested query. The odd thing is that the countryID always outputs 1, but when you click on a different group, the correct country is listed.

           protected Cursor getChildrenCursor(Cursor groupCursor) {
                    String countryID = Integer.toString(groupCursor.getInt(0));
                    SQLiteDatabase checkDB = null;
                    checkDB = SQLiteDatabase.openDatabase(DB_PATH, null, SQLiteDatabase.OPEN_READONLY);

Cursor value = checkDB.rawQuery("SELECT * FROM table WHERE open ='1' AND countries='"+ countryID +"'", null);

String test = "";
                    if(value.moveToFirst())
                            test =  value.getInt(0) + ": " + value.getString(1);
                    while(value.moveToNext()){
                            test += ";" + value.getInt(0) + ": " + value.getString(1);
                    }
                    checkDB.close();
                    return value;
            }

There is no problem with your query and the results you get are completely valid. Consider the following scenario:

table:
| open |   country          |
_____________________________
|  0   |  <your country id> |
|  1   |   <another id>     |

Then your first two queries do return some results, but your third query doesn’t.
Check your data and make sure you do have a row with both open == 1 and the correct country ID.

If you wish to return all rows from one of the first two queries, then you should convert your AND to OR. You will get the combined result of the first two queries (in my example table above, two rows).

Namely

Cursor value = checkDB.rawQuery("SELECT * FROM table WHERE open ='1' OR country='"+ countryID +"'", null);