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

Dannadannel

-Display the select command in a messagebox to see what comes up. I think your join might be ok, and the problem is with quotation marks or something. – Hoon

If I recall correctly, the access syntax has you enclosing each layer with parenthesis so try FROM [tableCourse] INNER JOIN [tableGrade] ON [tableCourse].[grading] = [tableGrade].[id] (INNER JOIN [tableCourseType] ON [tableCourse].[course_type] = [tableCourseType].[id]) – Cobbler

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.

Modred

This was exactly what I was looking for. It kept telling me some syntax or join was incorrect when it would work perfectly on SQL Management studio. – Mcswain

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

Congress

"specifically, the Jet db engine" -- not true: the same applies to the Access Database Engine (ACE) version of the engine. – Tabaret

My point was that the requirement for parentheses comes from the db engine, not the application. But you are correct, the requirement also applies to ACE/ADE/whatever-MS-is-calling-it-these-days. I updated my answer accordingly. – Congress

You should never use string concatenation to form SQL statements and queries, use parameters instead. – Tabina

Answer 3

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

Surefooted