Access数据库多表LEFT JOIN缺失运算符语法错误排查与解决

这个坑我之前也踩过！本质是Access的Jet SQL对多表LEFT JOIN的语法要求比SQL Server严格得多——它要求每新增一个LEFT JOIN都必须把之前的关联结果用括号包裹起来，而SQL Server支持直接链式写JOIN，不需要额外嵌套。

你遇到的错误场景 #

你的原始SQL在SQL Server能正常运行，但Access直接抛出"missing operator syntax error"，对应的C#代码和SQL如下：

String sql = "Select tblStudent.StudentID,tblStudent.studentFirstName,tblStudent.studentLastName,tblSchool.schoolName,tblAgents.agentFirstName,tblAgents.agentLastName,tblAgents.agentID,tblGuardians.guardianFirstName,tblGuardians.guardianLastName,tblGuardians.guardianID,tblConsultants.consultantFirstName,tblConsultants.consultantLastName,tblConsultants.consultantID " + 
"FROM (tblStudent LEFT JOIN tblSchool " + 
"ON (tblStudent.schoolID = tblSchool.schoolID)) " + 
"LEFT JOIN tblAgents " + 
"ON (tblStudent.agentID = tblAgents.agentID) " + 
"LEFT JOIN tblGuardians " + 
"ON (tblStudent.guardianID = tblGuardians.guardianID) " + 
"LEFT JOIN tblConsultants " + 
"ON (tblStudent.consultantID = tblConsultants.consultantID) " + 
"WHERE tblStudent.StudentID=@studentID";

m_dbConnection.Open();
using (OleDbCommand cmd = new OleDbCommand(sql, m_dbConnection)) {
    cmd.Parameters.AddWithValue("@StudentID", studentID);
    using (OleDbDataReader dr = cmd.ExecuteReader()) {
        while (dr.Read()) {
            lblSchool.Text = Convert.ToString(dr["schoolName"]);
            lblStudentFirstName.Text = Convert.ToString(dr["studentFirstName"]);
            lblStudentLastName.Text = Convert.ToString(dr["studentLastName"]);
            lblAgentFirstName.Text = Convert.ToString(dr["agentFirstName"]);
            lblAgentLastName.Text = Convert.ToString(dr["agentLastName"]);
            lblAgentID.Text = Convert.ToString(dr["agentID"]);
            lblGuardianFirstName.Text = Convert.ToString(dr["guardianFirstName"]);
            lblGuardianLastName.Text = Convert.ToString(dr["guardianLastName"]);
            lblGuardianID.Text = Convert.ToString(dr["guardianID"]);
            lblConsultantFirstName.Text = Convert.ToString(dr["consultantFirstName"]);
            lblConsultantLastName.Text = Convert.ToString(dr["consultantLastName"]);
            lblConsultantID.Text = Convert.ToString(dr["consultantID"]);
            lblID.Text = studentID;
        }
    }
}

而你发现只关联少量表时能正常运行，比如这个简化版SQL：

String sql2 = "Select tblStudent.StudentID,tblStudent.studentFirstName,tblStudent.studentLastName,tblSchool.schoolName,tblAgents.agentFirstName,tblAgents.agentLastName,tblAgents.agentID " + 
"FROM (tblStudent LEFT JOIN tblSchool " + 
"ON (tblStudent.schoolID = tblSchool.schoolID)) " + 
"LEFT JOIN tblAgents " + 
"ON (tblStudent.agentID = tblAgents.agentID) " + 
"WHERE tblStudent.StudentID=@studentID";

解决方法：给每一层LEFT JOIN添加嵌套括号 #

Access要求每一次LEFT JOIN都必须把前面所有的关联结果作为一个“整体”，用括号包裹后再和下一张表关联，形成层层嵌套的结构。修正后的SQL如下：

String sql = "Select tblStudent.StudentID,tblStudent.studentFirstName,tblStudent.studentLastName,tblSchool.schoolName,tblAgents.agentFirstName,tblAgents.agentLastName,tblAgents.agentID,tblGuardians.guardianFirstName,tblGuardians.guardianLastName,tblGuardians.guardianID,tblConsultants.consultantFirstName,tblConsultants.consultantLastName,tblConsultants.consultantID " + 
"FROM ((((tblStudent LEFT JOIN tblSchool " + 
"ON tblStudent.schoolID = tblSchool.schoolID) " + 
"LEFT JOIN tblAgents " + 
"ON tblStudent.agentID = tblAgents.agentID) " + 
"LEFT JOIN tblGuardians " + 
"ON tblStudent.guardianID = tblGuardians.guardianID) " + 
"LEFT JOIN tblConsultants " + 
"ON tblStudent.consultantID = tblConsultants.consultantID) " + 
"WHERE tblStudent.StudentID=@studentID";

简单来说，每新增一个LEFT JOIN，就把前面所有的JOIN语句用一对括号包起来——这是Access Jet SQL的硬性语法规则，和SQL Server的宽松语法差异导致了这个问题。

内容的提问来源于stack exchange，提问作者maximdj