Table tblSubject
| subId | subName |
|---|---|
| 1 | Mathematics |
| 2 | English |
Table tblTeachers
| teacherId | teacherName |
|---|---|
| 1 | Jean |
| 2 | Mary |
Table tblStudent
| studentId | studentName |
|---|---|
| 1 | Edward |
| 2 | Lauren |
Table tblStudentSubMap
| Id | studentId | teacherId | subId |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 2 | 2 | 1 | 1 |
| 3 | 1 | 2 | 1 |
| 4 | 2 | 2 | 1 |
| 5 | 1 | 1 | 2 |
| 6 | 2 | 1 | 2 |
| 7 | 2 | 2 | 2 |
I am trying to get the below output but unable to with various inner joins etc
Attempted query:
SELECT tblsubject.subname, tblTeachers.teacherName
FROM tablesubject, tblteachers
WHERE tblStudentSubMap.studentId=1
The WHERE condition input should be studentid and the corresponding subject name and teacher name should be returned.
Output
| id | tablesubject | teacherName |
|---|---|---|
| 1 | Mathematics | Jean |
| 2 | English | Mary |
for Studentid = 1
CodePudding user response：
There are various method on joining tables but you can try my below code
//Get columnName in every Table
SELECT map.Id,s.studentName,t.teacherName,sub.subName
FROM tblStudentSubMap map
LEFT JOIN tblStudent s ON s.studentID = map.studentID//Find Data with the same ID
LEFT JOIN tblTeacher t ON t.teacherID = map.teacherID//Find Data with the same ID
LEFT JOIN tblSubject sub ON sub.ID = map.subID;//Find Data with the same ID