CODEWITHSUNDEEP
androidsqliteandroid-sqlite
andro
andro

Reputation: 977

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

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);

Upvotes: 6

Views: 12879

Answers (5)

maimoona
maimoona

Reputation: 627

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

Upvotes: 6

Sammar javed
Sammar javed

Reputation: 153

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

Upvotes: 2

nheimann1
nheimann1

Reputation: 2398

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.

Upvotes: 2

Moin Ahmed
Moin Ahmed

Reputation: 2898

If you know the column name then you can find it like below,

long id = cursor.getLong(cursor.getColumnIndex("_id"));
String title = cursor.getString(cursor.getColumnIndex("title"));

if you just want to see all the columns name of the returned cursor then you can use String[] getColumnNames() method to retrieve all the column names.

Hope this will give you some hint.

Upvotes: 1

GrIsHu
GrIsHu

Reputation: 23638

I have used the following to do an inner join:

public Cursor innerJoin(Long tablebId) {
    String query = SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id WHERE name like '%c%';
    return database.rawQuery(query, null);
}

You can Iterate your cursor as below:

Cursor cursor = innerJoin(tablebId);
  String result = "";
  int index_CONTENT = cursor.getColumnIndex(KEY_CONTENT);
  cursor.moveToFirst();
  do{
     result = result + cursor.getString(index_CONTENT) + "\n";
    }while(cursor.moveToNext());

Hope this works for you

Upvotes: 1

Related Questions