Rick_F_ Rick_F_ - 5 months ago 9
Android Question

What is the query for retrieving a list of linked objects in a sqlite database with multiple tables?

I have made a sqlite database with the following tables:

public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE "+TABLE_CITY+"("
+KEY_ID+" INTEGER PRIMARY KEY, "
+KEY_CITY+" TEXT, "+KEY_COUNTRY+" TEXT)");
db.execSQL("CREATE TABLE "+TABLE_DIARY+"("
+KEY_ID+" INTEGER PRIMARY KEY, "
+KEY_DATE+" TEXT, "+KEY_STORY+" TEXT)");
db.execSQL("CREATE TABLE "+TABLE_CITY_DIARY+"("
+KEY_ID+" INTEGER PRIMARY KEY, "
+KEY_CITY_ID+" TEXT, "+KEY_DIARY_ID+" TEXT)");
}


Now I want to retrieve a list of all the diary entries that belong to a certain city (e.g. Barcelona contains 3 diary entries and Rome 5, but I only want a list of the 3 belonging to Barcelona).
I made a method for this, but I'm not sure what the query should be.

public List<Diary> getDiariesByCity(String city_name){
List<Diary>diaries=new ArrayList<Diary>();
SQLiteDatabase db=this.getReadableDatabase();
Cursor curs=db.rawQuery("SELECT * FROM "+TABLE_DIARY........,
new String[]{city_name});
if(curs.moveToFirst()){
do{
Diary diary=new Diary();
diary.setId(curs.getInt(curs.getColumnIndex(KEY_ID)));
diary.setDate(curs.getString(curs.getColumnIndex(KEY_DATE)));
diary.setStory(curs.getString(curs.getColumnIndex(KEY_STORY)));
diaries.add(diary);
}while(curs.moveToNext());
}return diaries;
}

Answer

You should use INNER JOIN like this:

Cursor curs=db.rawQuery("SELECT * FROM " +  TABLE_CITY_DIARY + 
+ " INNER JOIN " + TABLE_CITY + " ON " + TABLE_CITY_DIARY + "." + KEY_CITY_ID + "=" + TABLE_CITY + "." + KEY_ID 
+ " INNER JOIN " + TABLE_DIARY + " ON " + TABLE_CITY_DIARY + "." + KEY_DIARY_ID + "=" + TABLE_DIARY + "." + KEY_ID 
+ " WHERE " +  TABLE_CITY + "." + KEY_CITY + "=?", new String[]{city_name});
Comments