代码之家 › 专栏 › 技术社区 › Aya Sato

为什么在“WHERE”附近的查询中出现语法错误?(多个表)

multiple-tables left-join cursor jquery java

-1

Aya Sato · 技术社区 · 8 年前

这方面我还是新手,这是我第一次使用这些多表查询。为什么我会犯这样的错误?这是我的代码:

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

错误:

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 =?

2 回复 | 直到 8 年前

Racil Hilan 8 年前

您似乎有复制/粘贴问题。删除最后一个 left outer join 之前 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 });

我会对其进行稍微不同的格式化,主要是为了可读性:

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

Zachary 8 年前

虽然我不确定您在查询中使用的是什么结构化语言,但问题似乎在于您使用的联接运算符。Oracle文档 LEFT OUTER JOIN 显示了它的正确用法,其中语法为:

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

最后一个左外部联接直接位于WHERE子句之前,而不定义TableExpression。删除LEFT OUTER JOIN子句以删除错误。

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