I have these tables
subjectId(primary key) name location
subjectId(primary key) subjectName totalmarks
teacherId(primary key) teacherName
studentId(foreign key from studentInfo) subjectId(foreign key from subjectInfo) marks attendence
studentId(foreign key from studentInfo) subjectId(foreign key from subjectInfo) professorId(foreign key from professorInfo)
There is a many-to-many relation between student, professor, subject.
a student can have many subject to enroll with and can be taught by many
a teacher can teach many subject and many student
a subject can be enroll by many student and can be taught by many teachers
Is that junction table correct or should I have 2 junction tables?
What is the SQL query to find the report for student on the basis of roll no? The detail in report should be subjectname, subjectmarks, subject, attendence, professor name who taught this subject
The query that I tried returns redundant data.
select teacherInfo.teacherName, grade.marks, grades.attendence, subjectinfo.subjectName from teacherinfo join junctiontable on junctiontable.teacherId = teacherInfo.teacherId join studentinfo on studentinfo.studentId = junction.studentId join grades on grades.studentId = studentinfo.studentId where studentinfo.studentId = 1;