sqlite query using foreign keys

Question

Help a sqlite n00b out, please.

I have two tables

   "CREATE TABLE students (" +
          "_id       INTEGER PRIMARY KEY AUTOINCREMENT," +
          "studentname      TEXT not null," +
          "studentpic       BLOB," +
          "comment          TEXT);";

and

   "CREATE TABLE classes (" +
           "_id              INTEGER PRIMARY KEY AUTOINCREMENT," +
           "classname        TEXT," +
           "attend           INTEGER," +
           "late             INTEGER," +
           "dtime            TEXT," +
           "student          INTEGER REFERENCES students(_id) on
                             UPDATE CASCADE);";

I want to display a list of students in particular class, but my classes table only reference them by student-id, how can I construct a query to pull back into a single cursor all the fields of 'classes' table but use the actual names from 'students' table ?

Many thanks in advance

Answer 1

You can use the rawquery method with the following SQL code:

SELECT classes._id, students.studentname, classes.classname, classes.attend, classes.late, classes.dtime
FROM students, classes
WHERE students._id=classes.student

In short, by selecting from two tables, you join them, but you have to specify how they are joined. The part after the SELECT tells what you want as your output. The FROM shows which tables are involved (whether or not the parts of those tables are shown in the output). By default, if you give it two tables, all possible combinations of one row from the first table and one from the second are created, so you need a criteria in the WHERE clause which narrows it down to only match up the rows from the students table with the corresponding row in the classes table with the matching id. This works whether or not there is a foreign key relationship defined.

If you have additional criteria for the WHERE clause, you can add this by using AND. For example:

WHERE students._id=classes.student AND classes.classname="Underwater Basket Weaving"

There's a good tutorial on the overall process of setting up and using databases in SQLite in Android (much of which you already seem to understand) here.