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.
How do I join two SQLite tables in my application?
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.