Geek Answers Handbook

Android: How to access the results from the cursor while executing an INNER JOIN?

I am using INNER JOIN in two tables, table1 and table2, from my SQLite database. How to access the results (columns of both tables) from the cursor? Two tables have 2 columns with the 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);
+6
source share
5 answers

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 id, c2 etc.

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

Editing a broken link: check the rawQuery metid 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

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

You can access the result, as well as any other query. The only difference is that it is possible to name conflicts, the same column name for both tables. To resolve these conflicts, you need to use the table name as a prefix.

for instance

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

If this approach does not work. You should write your request 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 one more general note: it is better not to use "Select * ...", it is preferable to write explicitly the column that you would like to select.

+2
source

I used the following for the 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 move the cursor as follows:

 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.

+1
source

If you know the name of the column, you can find it as shown below,

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

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

Hope this gives you some hint.

+1
source

More articles:


All Articles