How do I join two SQLite tables in my Android application?
android, sqlite
Solution
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.
Problem
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?