How to retrieve students data based on teachers ID

According to a simple scenario ER diagram as follows.

Based on that, I developed a database mapping as follows.

In there, orange color columns shows the primary key of the tables and yellow color columns shows foreign key of the tables. Is this mapping correct? Now my problem is I need to retrieve students data who learned from some teacher, which means need to retrieve some teacher’s sudents who learned from his/her.

Answer

You need to learn about how to join tables using different types of available JOINs.

For your scenario , try like following, it will give you Student and the Teacher mapping.

select s.*
tbl_student s
inner join tbl_Course_Subjects tcs on s.Course_Id= tcs.Course_Id
inner join tbl_Subjects_Teacher tst on tst.SubjectId=tcs.Subject_Id
inner join tbl_Teacher t on t.Teacher_Id=tst.Teacher_Id
where t.Teacher_Id = @SomeTeacherId