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
This question is related to
mysql
database
inner-join
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
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
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
Source: Stackoverflow.com