CODEWITHSUNDEEP
androidsqlite
Joe Rakhimov
Joe Rakhimov

Reputation: 5083

Join tables in SQLiteQueryBuilder setTables

Now I have following code:

    SQLiteDatabase db=getReadableDatabase();
    SQLiteQueryBuilder qb=new SQLiteQueryBuilder();

    String[] sqlSelect={"0 _id","lang","value"};
    String sqlTables="gloss";

    qb.setTables(sqlTables);

    Cursor c=db.query(GL_TABLE, sqlSelect, "value like '%"+inputText+"%'", null, null, null, null, "20");

It is working perfect. Here this code is taking only one table. How to take data from 3 tables which are related with each other(entry is main node, sense is child of entry and gloss is child of sense):

entry(id,ent_seq); sense(id,fk); gloss(id,fk,lang,value);

Is it possible to do this with setTables? If it is, please give some suggestions how to do this.

I have done as @Anders8 said:

public Cursor getJoinedInfo(String lookingFor)
{
    Log.d(LOG_TAG, "DB: looking up info");

    SQLiteDatabase db=getReadableDatabase();

    Cursor cursor;
    String query;

    query="SELECT " +
            " e.id," +
            " s.id," +
            " s.fk," +
            " g.id," +
            " g.fk," +
            " g.lang," +
            " g.lang" +
            " FROM entry e" +
            "     INNER JOIN sense s ON e.id = s.fk" +
            "     INNER JOIN gloss g ON s.id = g.fk" +
            " WHERE g.value like '%"+lookingFor+"%'";

    Log.d(LOG_TAG, "DB: query = \n" + query.replace(", ",",\n  "));
    cursor=db.rawQuery(query,null);
    Log.d(LOG_TAG, "DB: query complete");

    return cursor;      
}

It is giving this error:

08-09 22:26:16.632: E/AndroidRuntime(9842): java.lang.IllegalArgumentException: column '_id' does not exist

What should I do?

Upvotes: 0

Views: 3463

Answers (2)

MotoRidingMelon
MotoRidingMelon

Reputation: 2567

It's going to be something like this:

public Cursor getJoinedInfo(String lookingFor)
{
    Log.d(TAG, "DB: looking up info");

    Cursor cursor;
    String query;

    query="SELECT " +
            " a.fieldName1," +
            " a.fieldName2," +
            " b.fieldName3," +
            " c.fieldName4" +
            " FROM tableName1 a" +
            "     INNER JOIN tableName2 b ON a.someKeyInA = b.someKeyInB" +
            "     INNER JOIN tableName3 c ON a.someOtherKeyInA = c.someKeyInC" +
            " WHERE a.FieldName ='" + lookingFor +"'";
    Log.d(TAG, "DB: query = \n" + query.replace(", ",",\n  "));
    cursor=db.rawQuery(query,null);
    Log.d(TAG, "DB: query complete");

    return cursor;      
}

Obviously, build the query you need. ;)

Upvotes: 3

Rarw
Rarw

Reputation: 7663

You can perform a join in SQLite. This will allow you to join all or only certain attributes from the tables you are working from into one final relation. That way you can select exactly what you're looking for.

I don't know what's in the data set or what you're looking for so I cannot write the query but I will say this - your best approach is to (1) write the query as a string (2) execute a raw database query using that string and (3) using the cursor to select the data from the result.

Upvotes: -1

Related Questions