SQL multiple join statement

When I had only one inner join in my SQL statement, it worked perfectly. I tried joining a second table, and now I am getting an error that says there is a syntax error (missing operator). What is wrong here?

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 & "'"
93030 次浏览
小开

MS Access (specifically, Jet/ACE) requires parentheses with multiple joins. Try:

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 & "'"
小开
最佳答案

For multi-table joins, you have to nest the extra joins in brackets:

SELECT ...
FROM ((origintable
JOIN jointable1 ON ...)
JOIN jointable2 ON ...)
JOIN jointable3 ON ...

basically, for every extra table you join past the first, you need a bracket before the original 'FROM' table, and a closing bracket on the matching JOIN 'on' clause.

小开

In spite of MS SQL Server, MS Access requires parentheses for a multiple JOIN statement. Basically, JOIN is an operation between two tables. When you have more than one JOIN, in fact, you are JOINing the result of the previous JOIN to another table. This logic is cascaded for any extra JOIN. For instance, if you have JOIN operations between 4 tables, you need to write it as follows:

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

提交答案