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

Solution 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

Solution 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.

Solution 3

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

Solution 4

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.

Solution 5

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

View more solutions

Comments

[nheimann1]

Using the column index is not a very good approach. Changing the column order in the select clause or adding another column in the future would make your function unstable. Always use the column name for long lasting code

[andro]

Thanks for the solution. I was aware this approach. Actually my problem was the two tables had two columns with same names and so was not able to retrieve the data. My bad for not specifying the question correctly. Got it solved.

[Al Lelopath]

The link provided by maimoon is broken (outdated). I did a search at the site for "cursor" and received no results, so not sure where it is.

[maimoona]

@AlLelopath : Updated with new links

[Teun Kooijman]

Came here specifically to find out what happends when names conflict once a join was performed, so thank you or that. For anyone who is confused about the like '%c%', this is SQLite syntax for pattern matching, where the % behaves like a "zero or many" wildcard. The pattern states "any record where name contains a c at any position".