Selecting from multiple tables?

Question

i have to tables in my database workouts and exercises i have the following methods getAllWorkouts and getAllExercises. Right these methods get the workouts and the exercises separately. How can i join these two together? so the workout will have all the exercises joined to it. This being a 1:M relationship.

getAllWorkouts

  public ArrayList<Workout> getAllWokouts(){
    ArrayList<Workout> workoutArrayList = new ArrayList<Workout>();
    SQLiteDatabase db = this.getReadableDatabase();

    try{
        String selectQuery = "SELECT * FROM " + TABLE_WORKOUT;
        db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);

        if (c != null && c.moveToFirst()){
            do {
                Workout workout = new Workout();
                workout.setId(c.getInt(0));
                workout.setName(c.getString(1));

                workoutArrayList.add(workout);


            } while (c.moveToNext());
        }

        return workoutArrayList;
} catch (SQLiteException se){
        Log.v("Exception",
                Log.getStackTraceString(se));

    } catch (Exception e){
    Log.v("Exception",
            Log.getStackTraceString(e));
    } finally {
        db.close();
    }
    return workoutArrayList;
    }

getAllExercises

ArrayList<Exercise> exerciseArrayList = new ArrayList<Exercise>();
    SQLiteDatabase db = this.getReadableDatabase();

    try{
        String selectQuery = "SELECT * FROM " + TABLE_EXERCISE;
        Cursor cursor = db.rawQuery(selectQuery, null);
        if (cursor != null && cursor.moveToFirst()){
            do {
                Exercise exercise = new Exercise();
                exercise.setId(cursor.getInt(0));
                exercise.setName(cursor.getString(1));
                exercise.setSet(cursor.getString(2));
                exercise.setWeight(cursor.getString(3));
                exercise.setReps(cursor.getString(4));

                exerciseArrayList.add(exercise);
            } while (cursor.moveToNext());
        }
        return exerciseArrayList;
    } catch (SQLiteException se){
        Log.v("Exception",
                Log.getStackTraceString(se));

    } catch (Exception e){
        Log.v("Exception",
                Log.getStackTraceString(e));
    } finally {
        db.close();
    }
        return exerciseArrayList;
    }

I have used the following code

public Cursor getAllWorkoutsExercises(){

    SQLiteDatabase db = this.getReadableDatabase();

    String SELECT_QUERY = "SELECT * FROM '" + TABLE_WORKOUT  + "'JOIN'" + TABLE_EXERCISE +  "' ON '" + EXERCISE_ID  + "' GROUP BY '" + EXERCISE_ID + "'ORDER BY'" + WORKOUT_NAME;   
    Cursor mCursor = db.rawQuery(SELECT_QUERY, null);

    return mCursor;
}

best way is to write a query using JOIN, so you can join the two tables in one, unique statement, that gets executed once and retrieve all the needed data — Gregorio Palamà, Apr 27 '18 at 13:53

score 0 · Answer 1 · answered Apr 27 '18 at 13:55

0

Try this: Cursor mCursor = db.rawQuery("SELECT * FROM Table1, Table2 " + "WHERE Table1.id = Table2.id_table1 " + "GROUP BY Table1.data1", null);

answered Apr 27 '18 at 13:55

Sasha Balyas

420
5
13

Maybe this link can help you: [link](https://stackoverflow.com/questions/11029538/sqlite-query-from-multiple-tables-using-sqlitedatabase) – Sasha Balyas Apr 27 '18 at 13:57
2

it would be better to stick to the standard SQL JOIN syntax. This one is not the standard one and it might not be supported anymore in future releases. The standard syntax would be "SELECT * FROM Table 1 JOIN Table2 ON Table1.id = Table2.id_table1 GROUP BY Table1.data1". – Gregorio Palamà Apr 27 '18 at 13:59

Selecting from multiple tables?

1 Answers1