Search code examples
androidcursorandroid-sqlite

Android SQLite: How to retrieve data from a table using the id's retrieved from other table

I have two tables say

TableA

 ________________________
|ADTMeasurement|ADTid  |
|--------------|-----  | 
|measurement   |1~1~3  |
|measurement   |1~1~12 |
|measurement   |1~1~7  |
|measurement   |1~1~11 |
|measurement   |1~1~99 |
------------------------

TableB

________________________________
|ADTName     |ADTid  |ADTType  |
|------------|-------|---------|
|ADTName_1   |1~1~3  |DTType_1 |
|ADTName_2   |1~1~12 |DTType_2 |
|ADTName_4   |1~1~7  |DTType_3 |
|ADTName_4   |1~1~11 |DTType_4 |
|ADTName_5   |1~1~99 |DTType_5 |
|ADTName_6   |1~1~10 |DTType_3 |
|ADTName_7   |1~1~4  |DTType_4 |
|ADTName_7   |1~1~6  |DTType_5 |
--------------------------------

How do I retrieve selected rows from tableB; for all the "ADTid" from tableA. I need to save the result in a JSONarray so that I can send the it to the server.

Below is the code which I tired to use, which is wrong.

public Cursor getSavedMeasuremnet() {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor mCursor = db.query("SELECT * FROM TableB WHERE ADTId IN (SELECT ADTId FROM TableA WHERE ADTMeasurment = 'measurement'");

        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

I am getting an error msg - cannot resolve method “query(java.lang.string)”

Solution

  • There is not query method with String only as parameter,you have to pass all the parameter in method as mention below

    enter image description here

    public JSONArray getSavedMeasuremnet() {
        JSONArray jsonArray=new JSONArray();

        SQLiteDatabase db = this.getWritableDatabase();

        Cursor mCursor = db.rawQuery("SELECT * FROM TableB JOIN TableA ON TableA.ADTid  == TableB.ADTid where TableA.ADTMeasurement='measurement'",null)

        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            try {
                    JSONObject jsonObject=new JSONObject();
                    jsonObject.put("ADTName",cursor.getString(cursor.getColumnIndex("ADTName")));
                    jsonObject.put("ADTid",cursor.getString(cursor.getColumnIndex("ADTid")));
                    jsonObject.put("ADTType",cursor.getString(cursor.getColumnIndex("ADTType")));
                    jsonArray.put(jsonObject);
                }  catch (JSONException e) {
                    e.printStackTrace();
                }
            cursor.moveToNext();
        }
        return jsonArray;
  }