How do I join two SQLite tables in my Android application?

Question

Background

I have an Android project that has a database with two tables: tbl_question and tbl_alternative.

To populate the views with questions and alternatives I am using cursors. There are no problems in getting the data I need until I try to join the two tables.

     Tbl_question       -------------     _id       question       categoryid   

     Tbl_alternative     ---------------     _id      questionid      categoryid      alternative 

I want something like the following:

SELECT tbl_question.question, tbl_alternative.alternative where  categoryid=tbl_alternative.categoryid AND tbl_question._id =  tbl_alternative.questionid.`  

This is my attempt:

public Cursor getAlternative(long categoryid) {             String[] columns = new String[] { KEY_Q_ID, KEY_IMAGE, KEY_QUESTION, KEY_ALT, KEY_QID};              String whereClause = KEY_CATEGORYID + "=" + categoryid +" AND "+ KEY_Q_ID +"="+ KEY_QID;              Cursor cursor = mDb.query(true, DBTABLE_QUESTION + " INNER JOIN "+ DBTABLE_ALTERNATIVE, columns, whereClause, null, null, null, null, null);              if (cursor != null) {                   cursor.moveToFirst();              }              return cursor; 

I find this way to form queries harder than regular SQL, but have gotten the advice to use this way since it is less error prone.

Question

How do I join two SQLite tables in my application?

Answer 1

You need rawQuery method.

Example:

private final String MY_QUERY = "SELECT * FROM table_a a INNER JOIN table_b b ON a.id=b.other_id WHERE b.property_id=?";  db.rawQuery(MY_QUERY, new String[]{String.valueOf(propertyId)}); 

Use ? bindings instead of putting values into raw sql query.

Answer 2

An alternate way is to construct a view which is then queried just like a table. In many database managers using a view can result in better performance.

CREATE VIEW xyz SELECT q.question, a.alternative      FROM tbl_question AS q, tbl_alternative AS a   WHERE q.categoryid = a.categoryid      AND q._id = a.questionid; 

This is from memory so there may be some syntactic issues. http://www.sqlite.org/lang_createview.html

I mention this approach because then you can use SQLiteQueryBuilder with the view as you implied that it was preferred.