Android: How to access results from Cursor when INNER JOIN is performed?

Question

I am using INNER JOIN on two tables,table1 and table2, from my SQLite Database. How do I access the results(columns of both tables) from the cursor? The two tables have 2 columns with same name.

        String query = SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id WHERE name like '%c%';
        Cursor c = newDB.rawQuery(query, null);

Answer 1

You can specify column names instead of using '*'.

String query = SELECT table1.id AS ID,table2.column2 AS c2,...... FROM table1 INNER JOIN table2 ON table1.id=table2.id WHERE name like '%c%';

and then access using column name ID,c2 etc .

while (cursor.moveToNext()) {
  String c2 = cursor.getString(cursor.getColumnIndex("c2"));
  int id = cursor.getInt(cursor.getColumnIndex("ID"));
  ..............
  .............
}

Editing the broken link : Check rawQuery methid here http://www.vogella.com/tutorials/AndroidSQLite/article.html and here http://www.codota.com/android/methods/android.database.sqlite.SQLiteDatabase/rawQuery for different examples

Answer 2

You can access the result as you would with any other query. The only difference is that there is a chance to name conflicts, same column name on both tables. In order to solve those conflict you would need to use the table name as a prefix.

For example

Long id = c.getLong(c.getColumnIndex(tableName1 + "." + idColumnName));

If this approach doesn't work. You should write your query as follows:

String query = SELECT table1.id AS table1_id FROM table1 INNER JOIN table2 ON table1.id=table2.id WHERE name like '%c%';
Cursor c = newDB.rawQuery(query, null);

And another general note, it is better not to use "Select *..." it is preferred to write explicitly which column you would like to select.

Answer 3

Cursor c=databseobject.functionname() //where query is used
if(c.movetofirst()) {
    do {
        c.getString(columnindex);
    } while(c.movetoNext());
}