SQL multiple join statement

Question

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 & "'" 

Answer 1

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.

Answer 2

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 & "'"