Syntax error on multiple join statement

Question

Hello, I'm getting a syntax error on this sql statement, can anyone advise thanks

String sql = "Select tblStudent.*,tblSchool.*,tblAgents.* " +
    "FROM tblStudent LEFT JOIN tblSchool " +
    "ON (tblStudent.schoolID = tblSchool.schoolID) " +
    "LEFT JOIN tblAgents " +
    "ON (tblStudent.agentID = tblAgents.agentID) " +
    "WHERE tblStudent.StudentID='" + studentID + "'";

I was hoping that I could do multiple joins

But I am getting a syntax error.

OK, you should never use * to select all fields especially when you have a join because you are sending the same data in multiple columns and that wastes server resources. You should list the columns you need and ONLY those columns. — HLGEM, Dec 06 '17 at 14:26
Inserting the StudentID like this, you are open to sql injection. Please use parameters. — Hans Keﬆing, Dec 06 '17 at 14:30
Parentheses used in a wrong way. Use query designer for building the query, then copy it to VBA — Sergey S., Dec 06 '17 at 14:34
Also I'd recommend to use `&` instead of `+` for string concatenation - in case of null value whole sting will be null and will generate an error. `&` works different way with null values — Sergey S., Dec 06 '17 at 14:38
Ok I think I have got it working now. Someone mentioned using parameters. How would I use parameters in the above statement? Example would be great thanks. — maximdj, Dec 06 '17 at 14:46

Edwin Rooks · Accepted Answer · 2017-12-06T14:54:40.827

For access, parenthesis with multiple joins means the following. If you have three joins, there are two left parenthesis after the from. The last join does not have a right parenthesis.

String sql = "Select tblStudent.*,tblSchool.*,tblAgents.* " +
"FROM (tblStudent LEFT JOIN tblSchool " +
"ON (tblStudent.schoolID = tblSchool.schoolID)) " +
"LEFT JOIN tblAgents " +
"ON (tblStudent.agentID = tblAgents.agentID) " +
"WHERE tblStudent.StudentID='" + studentID + "'";

Access SQL injection has been covered in other threads .

maximdj · Answer 2 · 2017-12-06T15:50:45.883

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

score 0 · Answer 3 · answered Dec 06 '17 at 16:13

I believe your final SQL should look like this:

SELECT tblStudent.*
    ,tblSchool.*
    ,tblAgents.*
FROM tblSchool
RIGHT JOIN (
    tblAgents RIGHT JOIN tblStudent ON tblAgents.agentID = tblStudent.agentID
    ) ON tblSchool.schoolID = tblStudent.schoolID
WHERE tblStudent.StudentID=111;

So, the VBA code for creating this SQL should be

Dim sql As String

sql = "SELECT tblStudent.* ,tblSchool.* ,tblAgents.* " & _
    "FROM tblSchool RIGHT JOIN (" & _
    "tblAgents RIGHT JOIN tblStudent ON tblAgents.agentID = tblStudent.agentID" & _
    ") ON tblSchool.schoolID = tblStudent.schoolID " & _
    "WHERE tblStudent.StudentID=" & studentID

Here I assume that studentID is numeric field. Also I would recommend to do not use * for selecting the data from more than one table, otherwise column names may be unpredictable and as mentioned in comments, it will require additional resources, which won't be used.

Syntax error on multiple join statement

3 Answers3