How get data related to InstructorID from table CourseClass

ahmed salah

9y847

Problem

How to get schedule for instructor from table CourseClass Table by sql query

I have 5 table as following :

Course table has relation with Instructor table many to many and generated table Instructor Course table .

Course table has relation with Class table many to many and generated table CourseClass Table table .

Instructor table

Instructor ID

Instructor Name

Instructor Course table

Instructor ID

Course ID

Course table

Course ID

Course Name

CourseClass Table

Course ID

Class ID

dayname

starttime

endtime

duration

Class

Class ID

Class Name

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

ahmed salah9y ago
Thank you for reply
In write the query as you tell me
SELECT     dbo.Class.*, dbo.Course_Class.*, dbo.Course.*, dbo.Inst_Course.*, dbo.Instructor.*
FROM         dbo.Class INNER JOIN
                      dbo.Course_Class ON dbo.Class.ClassID = dbo.Course_Class.ClassID INNER JOIN
                      dbo.Course ON dbo.Course_Class.CourseID = dbo.Course.CourseID INNER JOIN
                      dbo.Inst_Course ON dbo.Course.CourseID = dbo.Inst_Course.CourseID INNER JOIN
                      dbo.Instructor ON dbo.Inst_Course.InstID = dbo.Instructor.InstructorID
WHERE     (dbo.Inst_Course.InstID = 1)
Result of query is 4 rows but instructor 1 have 2 courses only
meaning result must be 2 not 4
I see this results is wrong
as bellow this is my result to instructor 1
Dilip Gupta9y ago
try like this

SELECT *
FROM dbo.tblClass INNER JOIN
dbo.tblCourseClass ON dbo.tblClass.ClassID = dbo.tblCourseClass.ClassID INNER JOIN
dbo.tblCourse ON dbo.tblCourseClass.CourseID = dbo.tblCourse.CourseID INNER JOIN
dbo.tblInstructorCourse ON dbo.tblCourse.CourseID = dbo.tblInstructorCourse.CourseID INNER JOIN
dbo.tblInstructor ON dbo.tblInstructorCourse.InstructorID = dbo.tblInstructor.InstructorID
where dbo.tblInstructorCourse.InstructorID=1

if solved then mark answer as accepted
ahmed salah9y ago
my diagram as following
ahmed salah9y ago
to be clear i need to show select * from CourseClass Table where instructorid =instructorid