Search code examples
androidandroid-sqliteinner-join

sqlLiteDatabase.query() for INNER JOIN

I want to do a query as follows from an Android SQLite db:

select place.permit_name, local.local_distance
from tblLocal local
inner join tblPlaces place
on place._id = local._id
order by local.local_distance asc

I'm trying to use query(), instead of rawQuery(). I don't know how to specify the INNER JOIN/ON. How do I do this?

final String table = PlacesDataContract.PlacesDataEntry.TABLE_NAME;
final String[] columns =  { PlacesDataContract.PlacesDataEntry.COLUMN_NAME_PERMIT_NAME. , LocalDataContract.LocalDataEntry.COLUMN_NAME_LOCAL_DISTANCE};
final String orderBy = LocalDataContract.LocalDataEntry.COLUMN_NAME_LOCAL_DISTANCE + " ASC"; 

Cursor cursor = sqLiteDatabase.query(table, // table
        columns, // columns
        null, // selection
        null, // selectionArgs
        null, // groupBy
        null, // having
        orderBy, // orderBy
        null); // limit
Solution

  • You can put the join in the table variable.

    String table = "tblLocal local " +
    "inner join tblPlaces place " +
    "on place._id = local._id";

    See the IOSched app from Google for an example. Take a look at the provider package and the SelectionBuilder class.

    A SQLiteQueryBuilder is used to construct the query string, and all it does is concatenate the table variable to the rest of the query. See https://android.googlesource.com/platform/frameworks/base/+/refs/heads/master/core/java/android/database/sqlite/SQLiteQueryBuilder.java#201