SQLiteException: near "FROM": syntax error multiple Joins

Question

Can anyone see if there is an error in my query here, it's my first attempt at multiple Joins, below is the logcat error. Thanks in advance

android.database.sqlite.SQLiteException: near "FROM": syntax error (code 1 SQLITE_ERROR[1]): , while compiling: SELECT SUM(A.quantity), A.ingredient, A.recipe, B.ingredient_type, B.measurement_name, C.id, D.plan_name, FROM QUANTITY AS A JOIN INGREDIENTS AS B ON A.ingredient = B.ingredient_name JOIN PLAN_RECIPES AS C ON A.recipe = C.recipe_name JOIN MEAL_PLAN AS D ON C.id = D.plan_recipe GROUP BY A.ingredient WHERE D.plan_name LIKE ?

Code

    public void loadIngredient() {
    shopList.clear();
    db = (new DatabaseManager(this).getWritableDatabase());
    String RECIPE_SEARCH = " SELECT SUM(A.quantity), A.ingredient, A.recipe, B.ingredient_type, B.measurement_name, C.id, D.plan_name, " +
            "FROM " + DatabaseManager.TABLE_QUANTITY + " AS A JOIN " + DatabaseManager.TABLE_INGREDIENTS +
            " AS B ON A.ingredient = B.ingredient_name" + " JOIN " + DatabaseManager.TABLE_PLAN_RECIPES + " AS C ON A.recipe = C.recipe_name " +
            " JOIN " + DatabaseManager.TABLE_MEAL_PLAN + " AS D ON C.id = D.plan_recipe GROUP BY A.ingredient";
    String selectQuery = "";
    selectQuery = RECIPE_SEARCH + " WHERE D.plan_name LIKE ?";
    c = db.rawQuery(selectQuery, new String[]{"%" + search + "%"});
    if (c.moveToFirst()) {
        do {
            Shopping_List shopping_list = new Shopping_List();
            shopping_list.setQuantity(c.getDouble(c.getColumnIndex("quantity")));
            shopping_list.setIngredient_name(c.getString(c.getColumnIndex("ingredient_name")));
            shopping_list.setIngredient_type(c.getString(c.getColumnIndex("ingredient_type")));
            shopping_list.setMeasurement_name(c.getString(c.getColumnIndex("measurement_name")));
            shopList.add(shopping_list);
        } while (c.moveToNext());
        c.close();
    }

}

Answer 1

There are several problems in your code.

The 1st is a comma that you must remove after D.plan_name inside the variable RECIPE_SEARCH right before the FROM clause.

The 2nd is the WHERE clause that must precede the GROUP BY clause.

The 3d is that you must alias the column SUM(A.quantity) that is returned by your query so you can retrieve it by that alias, say quantity.

The 4th is that there is no column ingredient_name returned by your query, but I assume this is the column A.ingredient which should be aliased to ingredient_name.

So change to this:

public void loadIngredient() {
    shopList.clear();
    db = (new DatabaseManager(this).getWritableDatabase());
    String RECIPE_SEARCH = 
        "SELECT SUM(A.quantity) quantity, A.ingredient ingredient_name, A.recipe, B.ingredient_type, B.measurement_name, C.id, D.plan_name " +
        "FROM " + DatabaseManager.TABLE_QUANTITY + " AS A JOIN " + DatabaseManager.TABLE_INGREDIENTS + " AS B ON A.ingredient = B.ingredient_name " + 
        "JOIN " + DatabaseManager.TABLE_PLAN_RECIPES + " AS C ON A.recipe = C.recipe_name " +
        "JOIN " + DatabaseManager.TABLE_MEAL_PLAN + " AS D ON C.id = D.plan_recipe " +
        "WHERE D.plan_name LIKE ? GROUP BY A.ingredient";   
    c = db.rawQuery(RECIPE_SEARCH, new String[]{"%" + search + "%"});

    if (c.moveToFirst()) {
        do {
            Shopping_List shopping_list = new Shopping_List();
            shopping_list.setQuantity(c.getDouble(c.getColumnIndex("quantity")));
            shopping_list.setIngredient_name(c.getString(c.getColumnIndex("ingredient_name")));
            shopping_list.setIngredient_type(c.getString(c.getColumnIndex("ingredient_type")));
            shopping_list.setMeasurement_name(c.getString(c.getColumnIndex("measurement_name")));
            shopList.add(shopping_list);
        } while (c.moveToNext());
    }
    c.close();
    db.close();
}

Also, your query returns columns that you don't use in the loop, which I did not remove, by you may remove them.