Why am I having an syntax error in my query near "WHERE"? (Multiple tables)

Question

Im still new on this and it's my first time using these multiple tables query. Why am I having such an error? Here is my Code:

 String selectQuery =
            " select *" +
            "    from tableassign left outer join\n" +
            "         tableacc\n" +
            "         on tableassign.signeeid = tableacc.userid left outer join\n" +
            "         tableinfo\n" +
            "         on tableassign.signeeid = tableinfo.userid left outer join\n" +
                    " WHERE tableassign.signeedepid =?";
    Cursor data = db.rawQuery(selectQuery , new String[]{ signeedep });

Error:

android.database.sqlite.SQLiteException: near "WHERE": syntax error (code 1):
 , while compiling: select *  from tableassign left outer join  tableacc on
 tableassign.signeeid = tableacc.userid left outer join  tableinfo on 
tableassign.signeeid = tableinfo.userid left outer join WHERE 
tableassign.signeedepid =?

Try removing the last "left outer join" – Zachary Dec 30 '17 at 02:38 — Zachary, Dec 30 '17 at 02:38

score 1 · Accepted Answer · answered Dec 30 '17 at 02:41

It seems you have a copy/paste issue. remove the last left outer join before where:

String selectQuery =
        " select *" +
        "    from tableassign left outer join\n" +
        "         tableacc\n" +
        "         on tableassign.signeeid = tableacc.userid left outer join\n" +
        "         tableinfo\n" +
        "         on tableassign.signeeid = tableinfo.userid\n" +
                " WHERE tableassign.signeedepid =?";
Cursor data = db.rawQuery(selectQuery , new String[]{ signeedep });

And I would format it a bit differently, mainly for readability:

String selectQuery =
        " SELECT *\n" +
        " FROM tableassign\n" +
        " LEFT JOIN tableacc ON tableassign.signeeid = tableacc.userid\n" +
        " LEFT JOIN tableinfo ON tableassign.signeeid = tableinfo.userid\n" +
        " WHERE tableassign.signeedepid =?";
Cursor data = db.rawQuery(selectQuery , new String[]{ signeedep });

score 1 · Answer 2 · answered Dec 30 '17 at 02:45

While I am unsure exactly what structured language you are using for the queries, the problem appears to be with your use of the JOIN operator. The Oracle documentation on LEFT OUTER JOIN shows the correct usage of it, in which the syntax is:

TableExpression LEFT [ OUTER ] JOIN TableExpression
{
    ON booleanExpression |
    USING clause
}

The final LEFT OUTER JOIN immediately precedes the WHERE clause without defining a TableExpression. Remove the LEFT OUTER JOIN clause to remove the error.

 String selectQuery =
            " select *" +
            "    from tableassign left outer join\n" +
            "         tableacc\n" +
            "         on tableassign.signeeid = tableacc.userid left outer join\n" +
            "         tableinfo\n" +
            "         on tableassign.signeeid = tableinfo.userid" +
                    " WHERE tableassign.signeedepid =?";
    Cursor data = db.rawQuery(selectQuery , new String[]{ signeedep });

Why am I having an syntax error in my query near "WHERE"? (Multiple tables)

2 Answers2