CODEWITHSUNDEEP
sqlms-access
Louay Hamada
Louay Hamada

Reputation: 771

INNER JOIN to more than one table

I have four tables

student
-------
id, int
firstName, varchar
lastName, varchar

course
------
id, int
name, varchar

semester
--------
id, int
name, varchar

mark
----
studentId, int
courseId, int
semesterId, int
mark, int

I want to make a sql query that retrives firstName, lastName, courseName, semesterName and mark for every row in the mark table.

I tried to do it with INNER JOIN but I can't make INNER JOIN for more than one table.

That code I reached finally

select student.firstName, student.lastName, course.name, semester.name, mark.mark
from mark
INNER JOIN student ON student.id = mark.studentId
INNER JOIN course ON course.id = mark.courseId
INNER JOIN semester ON semester.id = mark.semesterId

Upvotes: 1

Views: 420

Answers (2)

devarc
devarc

Reputation: 1157

Try theta style join:

select student.firstName, student.lastName, course.name, semester.name, mark.mark
from mark, student ,course, semester
WHERE  student.id = mark.studentId AND course.id = mark.courseId AND semester.id = mark.semesterId

Upvotes: 1

Taryn
Taryn

Reputation: 247630

In Ms Access you have to include parentheses in a query with multiple joins:

select st.firstName, st.lastName, c.name, sm.name, m.mark
from (((mark m
        INNER JOIN student st ON st.id = m.studentId)
        INNER JOIN course c ON c.id = m.courseId)
        INNER JOIN semester sm ON sm.id = m.semesterId)

Upvotes: 1

Related Questions