1

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();
    }

}
Improve this question
5
  • 1
    Remove the , after D.plan_name in the string RECIPE_SEARCH. Commented Oct 3, 2020 at 14:34
  • android.database.sqlite.SQLiteException: near "WHERE": 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 ? Commented Oct 3, 2020 at 14:49
  • 1
    Put the WHERE clause before the GROUP BY clause. Commented Oct 3, 2020 at 14:50
  • Failed to read row 0, column -1 from a CursorWindow which has 34 rows, 7 columns. Commented Oct 3, 2020 at 15:00
  • See my answer... Commented Oct 3, 2020 at 15:19

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

17 Comments

I'm still getting Couldn't read row 0, col -1 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.
Check again the column names/aliases. They must be exactly the same a the ones inside the loop: quantity, ingredient_name, ingredient_type, measurement_name. You can remove 1 each time to spot which one is not the same.
I can't see any issues with these
Did you copy the code as it is? Did you try by removing 1 column at a time from the loop? first remove shopping_list.setQuantity(c.getDouble(c.getColumnIndex("quantity"))); and run. Then remove shopping_list.setIngredient_name(c.getString(c.getColumnIndex("ingredient_name"))); etc
Why did you change the alias to ingredient_quantity? In this line: shopping_list.setQuantity(c.getDouble(c.getColumnIndex("quantity"))); you are trying to retrieve it withe the name quantity.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.