Android SQLite returns no results using WHERE clause

Question

I have the below sqlite query running, I know it many not be the best way to do it, but I am just trying to get this to work at the moment.

If I run these two queries I get 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 the two where clauses I get no results. Have I over looked something simple?

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

It turns out that I should have considered it was something to do with being in an expandable listview. Below is the full code where the query is nested. The odd thing is the countryID always outputs 1, but when you click on a different group the correct coutries are 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;
            }

Answer 1

Your queries are fine, and the results you're getting are perfectly valid. Consider the following situation:

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

Then your two first queries would indeed return some result, but your third would not. Check your data, and make sure you actually have rows with open == 1 and the correct country id at the same time.

If you wish to return all the rows from either one of your first two queries, then you should turn your AND into an OR. And you will get the combined results of your two first queries (in my example table above, the two rows)

i.e.

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

Answer 2

There is nothing wrong with the query itself, but are you sure there are rows that has columns with open=1 AND country=whateverthevalue, at the same time? If not, you will of course not get any result.

Run "adb shell", open your databasefile with sqlite3 and run the query there to check.