CODEWITHSUNDEEP
sqlms-accessinner-join
user2288263
user2288263

Reputation: 11

Trouble with parenthesis in MS Access for SQL Inner Joins

Have tried the below SQL in MS Access but cannot seem to get it working, anyone got a better idea?

SELECT top 4 Student.STUDENT_DEGREE, Student.STUDENT_SEX,STUDENT_GROUP_ID,STUDENT_GROUP_ID2,RESULT_MARK
FROM (((Student)
INNER JOIN Result ON Student.STUDENT_ID=Result.RESULT_STUDENT_ID)
INNER JOIN Group ON RESULT_GROUP_ID = GROUP_ID)
where STUDENT_GROUP_ID <> ''
order by Result.RESULT_MARK desc;

Whenever i run this i just get the error:

Syntax error in FROM clause

Upvotes: 1

Views: 2593

Answers (2)

HansUp
HansUp

Reputation: 97101

Group is a reserved word. Enclose that name in square brackets to avoid confusing the db engine. You can also assign an alias for the table name.

FROM
    (Student
    INNER JOIN Result
    ON Student.STUDENT_ID=Result.RESULT_STUDENT_ID)
    INNER JOIN [Group] AS g
    ON Result.RESULT_GROUP_ID = g.GROUP_ID

I had to guess which tables contain those fields in the last ON clause. If you set up the joins in Design View of the Access query designer, it will help you get the names right. It will also add the parentheses which the db engine requires for any query which includes more than one join.

Also qualify the table sources for the field names in your SELECT list and elsewhere in the query. Here again, the query designer can supply the correct names for you.

Upvotes: 5

PinnyM
PinnyM

Reputation: 35531

Remove the extra set of parentheses around Student:

SELECT top 4 Student.STUDENT_DEGREE,Student.STUDENT_SEX,STUDENT_GROUP_ID,STUDENT_GROUP_ID2,RESULT_MARK
FROM ((Student
INNER JOIN Result ON Student.STUDENT_ID=Result.RESULT_STUDENT_ID)
INNER JOIN Group ON RESULT_GROUP_ID = GROUP_ID)
where STUDENT_GROUP_ID <> ''
order by Result.RESULT_MARK desc;

Upvotes: 2

Related Questions