CODEWITHSUNDEEP
androidandroid-sqlite
Manoj
Manoj

Reputation: 2829

SQLite Android Inner join

I have one strange issue. I am fetching data from server and inserting it into tables. After insertion I query that data using inner join between two tables. here is my query : 

Select 
    F._id, F.id, F.logo, F.total_like, F.distance, F.store_name, F.mob_no_1,
    F.mob_no_2, F.mob_no_3, F.tel_no_1, F.tel_no_2, F.tel_no_3, F.description,
    R.total_record, R.total_page, R.current_page 
from 
    FAVOURITE_STORES as F 
    INNER JOIN FAVOURITE_RECORDS as R on F.area_id = R.area_id 
where 
    F.area_id = 2 and 
    R.area_id = 2

I get the cursor count as 1 on some devices and on some devices I get cursor count as zero. Even if the data is there in table.

here is my select query function

public Cursor rawQuery(String sQuery,String[] selectionArgs) {
        if(mDatabase == null) {
            mDatabase = getWritableDatabase();
        }
        debug("Query "+sQuery);
        return mDatabase.rawQuery(sQuery,selectionArgs);
    }

Cursor class 

public class ExampleCursorLoader extends CursorLoader {
    private Activity mActivity;
    private String msQuery;
    private DBUtil mDbUtil;
    private String[] mSelectionArgs;

    public ExampleCursorLoader(Activity context, String query,String[] selectionArgs) {
        super(context);
        this.mActivity = context;
        this.msQuery = query;
        this.mSelectionArgs = selectionArgs;
        this.mDbUtil = DBUtil.getInstance(mActivity.getApplicationContext());
    }

    public ExampleCursorLoader(Activity context, String query) {
        this(context,query,null);
        debug(query);
    }
    public Cursor loadInBackground() {  
        debug("Loading in Background");
        Cursor cursor=null;
        cursor = mDbUtil.rawQuery(msQuery,mSelectionArgs);
        return cursor;
    }

    private void debug(String s) {
        Log.v("Adapter " , "Adapter " + s);
    }

    protected void onStartLoading() {
        forceLoad();
        debug("Started Loading");
    }

    protected void onStopLoading() {
        super.onStopLoading();
    }


}

and this is how I am calling it. 

return new ExampleCursorLoader(mActivity,sQuery);

Device that gives count as 1 is Samsung s3. And zero is Samsung Grand. Have any thought or suggestion on this?

Upvotes: 4

Views: 3127

Answers (2)

Simon Dorociak
Simon Dorociak

Reputation: 33495

Device that gives count as 1 is Samsung s3. And zero is Samsung Grand. Have any thought or suggestion on this?

This kind of problem is hard to explain because most likely it doesn't throw any error but only shows different and unwanted result.

All what you are able to do is to "improve query":

At first use placeholders and not raw statements:

String query = "... where F.area_id = ? and R.area_id = ?";
String[] whereArgs = {"2", "2"};

Also you don't need to use AS clause, it's enough to say:

... from FAVOURITE_STORES F INNER JOIN FAVOURITE_RECORDS R on ...

From code that you provided everythings seems correct (query and an usage of rawQuery()).

Upvotes: 1

Smokez
Smokez

Reputation: 382

It seems to me that the last section of your WHERE-clause is unnecessary:

 and R.area_id=2

You've already specified the rows for the FAVOURITE_STORES table to match area_id=2. As the INNER JOIN is performed on that column, it will only return the rows from FAVOURITE_RECORDS where the area_id column matches FAVOURITE_STORES' area_id column.

I'm not sure how the SQLite engine handles this, but it's worth a try.

Upvotes: 0

Related Questions