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    quot   quot    categoryid   quot  AND  quot   KEY Q ID   quot   quot   KEY QID               Cursor cursor   mDb query true  DBTABLE QUESTION    quot  INNER JOIN  quot   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

User · Answer

In addition to  pawelzieba s answer  which definitely is correct  to join two tables  while you can use an INNER JOIN like this     SELECT   FROM expense INNER JOIN refuel ON exp id   expense id WHERE refuel id   1   via raw query like this -   String rawQuery    SELECT   FROM     RefuelTable TABLE NAME     INNER JOIN     ExpenseTable TABLE NAME             ON     RefuelTable EXP ID           ExpenseTable ID             WHERE     RefuelTable ID            id  Cursor c   db rawQuery          rawQuery          null      because of SQLite s backward compatible support of the primitive way of querying  we turn that command into this -   SELECT   FROM expense  refuel WHERE exp id   expense id AND refuel id   1   and hence be able to take advanatage of the SQLiteDatabase query   helper method   Cursor c   db query          RefuelTable TABLE NAME           ExpenseTable TABLE NAME          Utils concat RefuelTable PROJECTION  ExpenseTable PROJECTION           RefuelTable EXP ID           ExpenseTable ID     AND     RefuelTable ID            id          null          null          null          null      For a detailed blog post check this  http   blog championswimmer in 2015 12 doing-a-table-join-in-android-without-using-rawquery

User · Answer

Ambiguous column  usually means that the same column name appears in at least two tables  the database engine can t tell which one you want  Use full table names or table aliases to remove the ambiguity    Here s an example I happened to have in my editor  It s from someone else s problem  but should make sense anyway   select P    from product has image P inner join highest priority images H          on  H id product   P id product and H priority   p priority

User · Answer

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

User · Answer

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

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

Examples related to android

Examples related to sqlite