SQL query using foreign keys

Question

Help sqlite n00b, 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 a specific class, but my class table only refers to the student-id identifier, how can I build a query to pull all the fields of the classes table back into one cursor, but use the actual names from the students table?

Thank you very much 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, choosing from two tables, you join them, but you must specify how they are combined. The part after SELECT tells you what you want as output. FROM shows which tables are involved (regardless of whether parts of these 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 to have a criterion in the WHERE clause that narrows it to match only rows from the students table with the corresponding row in class table with matching identifier. This works regardless of whether the foreign key relationship is defined.

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

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

There is a good tutorial on the general process of setting up and using databases in SQLite on Android (much of what you already understand) here .