Inner join with 3 tables in mysql

Question

I want to select data from more tables with Inner join.

These are my tables.

Student (studentId, firstName, lastname)
Exam (examId, name, date)
Grade (gradeId, fk_studentId, fk_examId, grade)

I want to write a statement that shows which exam, grade and date alle the students have been to. Sorted after date.

This is my statement. It runs, but i want to make sure that i am doing it correctly.

SELECT
  student.firstname,
  student.lastname,
  exam.name,
  exam.date,
  grade.grade
FROM grade
  INNER JOIN student
    ON student.studentId = grade.gradeId
  INNER JOIN exam
    ON exam.examId = grade.gradeId
ORDER BY exam.date

it looks strange that column gradeID is connected to studentID? but if you put structure that way and it return results you expected then i guess it is fine ... — Bojan Kovacevic, Apr 15 '13 at 10:55
Yes, you are doing it correctly. Not much point to the question though if it works and gives the expected results right? — Niels Keurentjes, Apr 15 '13 at 10:56
I am new to mysql, and want to be sure that im doing in right. A lot of times i have made some statements that got the right thing. But it wasn´t the correct method. — Zincktest, Apr 15 '13 at 10:57
to me it looks that student.studendId = grade.fk_studentId and that Exam.examId = grade.fk_examId — Bojan Kovacevic, Apr 15 '13 at 10:57
Yes you are correct http://stackoverflow.com/questions/14673338/mysql-inner-join-query-multiple-tables — Sooraj Chandu, Apr 15 '13 at 10:58
post a table description in ur question. use query `describe grade` — Suhel Meman, Apr 15 '13 at 11:13

score 76 · Accepted Answer · answered Apr 15 '13 at 10:57

76

Almost correctly.. Look at the joins, you are referring the wrong fields

SELECT student.firstname,
       student.lastname,
       exam.name,
       exam.date,
       grade.grade
  FROM grade
 INNER JOIN student ON student.studentId = grade.fk_studentId
 INNER JOIN exam ON exam.examId = grade.fk_examId
 ORDER BY exam.date

answered Apr 15 '13 at 10:57

agim

1,841
12
19

When a run your statement. Is says Error code: 1054. unknown columns 'grade.fk_studentId' in ' on clause' – – Zincktest Apr 15 '13 at 11:09
1

same answer as @AjoKoshy gave you! It seams you don't have a field `grade.fk_studentId` so you have to use the right field instead. – agim Apr 15 '13 at 11:11

score 17 · Answer 2 · answered Apr 15 '13 at 10:56

17

The correct statement should be :

SELECT
  student.firstname,
  student.lastname,
  exam.name,
  exam.date,
  grade.grade
FROM grade
  INNER JOIN student
    ON student.studentId = grade.fk_studentId
  INNER JOIN exam
    ON exam.examId = grade.fk_examId
ORDER BY exam.date

A table is refered to other on the basis of the foreign key relationship defined. You should refer the ids properly if you wish the data to show as queried. So you should refer the id's to the proper foreign keys in the table rather than just on the id which doesn't define a proper relation

answered Apr 15 '13 at 10:56

Ajo Koshy

1,205
2
21
33

When a run your statement. Is says Error code: 1054. unknown columns 'grade.fk_studentId' in ' on clause' – Zincktest Apr 15 '13 at 11:04
1

@user2231285 you gave the tabledefinition for Grade as `Grade (gradeId, fk_studentId, fk_examId, grade)`. So it should have the column as written in the query – Ajo Koshy Apr 15 '13 at 11:05
1

this answer, not the other highly rated one on this topic. @AjoKoshy really great response, thanks. – ryan_m Feb 25 '19 at 23:48

score 3 · Answer 3 · answered Apr 15 '13 at 11:02

3

SELECT
  student.firstname,
  student.lastname,
  exam.name,
  exam.date,
  grade.grade
FROM grade
 INNER JOIN student
   ON student.studentId = grade.fk_studentId
 INNER JOIN exam
   ON exam.examId = grade.fk_examId
 GROUP BY grade.gradeId
 ORDER BY exam.date

answered Apr 15 '13 at 11:02

Suhel Meman

3,702
1
18
26

Please explain how `GROUP BY` clause is relevant here? – Payel Senapati Aug 11 '21 at 16:12

score 1 · Answer 4 · answered Aug 11 '21 at 16:11

A more professional way to write the code is by using "Aliases" -

SELECT
  s.firstname,
  s.lastname,
  e.name,
  e.date,
  g.grade
FROM grade AS g
 INNER JOIN student AS s
   ON s.studentId = g.fk_studentId
 INNER JOIN exam AS e
   ON e.examId = g.fk_examId
 ORDER BY e.date;

Inner join with 3 tables in mysql

4 Answers4

Linked