I am trying to build a query with more than 3 tables where data is scattered across

Issue

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

Solution

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

Answered By – Cyrille Con Morales

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published