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
– HLGEM, Commented Dec 6, 2017 at 14:26
Inserting the StudentID like this, you are open to sql injection. Please use parameters. — Hans Keﬆing
– Hans Keﬆing, Commented Dec 6, 2017 at 14:30
Parentheses used in a wrong way. Use query designer for building the query, then copy it to VBA — Sergey S.
– Sergey S., Commented Dec 6, 2017 at 14:34

Edwin Rooks · Accepted Answer · 2017-12-06 14:54:40Z

1

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 .

edited Dec 6, 2017 at 14:54

answered Dec 6, 2017 at 14:20

Edwin Rooks

567 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

maximdj · Accepted Answer · 2017-12-06 15:50:45Z

0

        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";

edited Dec 6, 2017 at 15:50

answered Dec 6, 2017 at 14:47

maximdj

3552 silver badges13 bronze badges

Comments

Sergey S. · Accepted Answer · 2017-12-06 16:13:57Z

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.

Collectives™ on Stack Overflow

Syntax error on multiple join statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related