当我的SQL语句中只有一个inner join时,它工作得很好。我尝试连接第二个表,但现在收到一个错误,指出存在语法错误(缺少运算符)。这里出了什么问题?
adsFormView.SelectCommand = "SELECT * FROM [tableCourse] INNER JOIN [tableGrade] ON [tableCourse].[grading] = [tableGrade].[id] INNER JOIN [tableCourseType] ON [tableCourse].[course_type] = [tableCourseType].[id] WHERE [prefix]='" & myPrefix & "' AND [course_number]='" & myCourseNum & "'"
发布于 2011-10-22 04:37:17
对于多表联接,您必须将额外的联接嵌套在括号中:
SELECT ...
FROM ((origintable
JOIN jointable1 ON ...)
JOIN jointable2 ON ...)
JOIN jointable3 ON ...
基本上,对于超过第一个表的每个附加表,您需要在原始'FROM‘表之前加上一个括号,并在匹配的join ' on’子句上加上一个结束括号。
发布于 2011-10-22 04:36:23
MS Access (特别是Jet/ACE)需要带多个联接的括号。尝试:
adsFormView.SelectCommand = _
" SELECT * FROM ([tableCourse] " & _
" INNER JOIN [tableGrade] " & _
" ON [tableCourse].[grading] = [tableGrade].[id]) " & _
" INNER JOIN [tableCourseType] " & _
" ON [tableCourse].[course_type] = [tableCourseType].[id] " & _
" WHERE [prefix]='" & myPrefix & "'" & _
" AND [course_number]='" & myCourseNum & "'"
发布于 2019-12-03 15:45:31
尽管有MS SQL Server,MS Access仍需要对多个JOIN语句使用括号。基本上,JOIN是两个表之间的操作。当您有多个连接时,实际上,您正在将前一个连接的结果JOINing到另一个表。对于任何额外的连接,此逻辑都是级联的。例如,如果您有4个表之间的连接操作,则需要编写如下代码:
SELECT * FROM
(
( Table1 JOIN Table2 ON Table1.column1 = Table2.column2) --result of JOIN is treated as a temp table
JOIN Table3 ON Table1.column1 = Table3.column3
) --result of JOIN is treated as another temp table
JOIN Table4 ON Table4.column4 = Table2.column2
https://stackoverflow.com/questions/7854969
复制相似问题