SQL Inner join many to many multi part identifier not bound-CodePudding

select Course.CourseName as course_coursename
    ,Instructor.Name as instructor_instructorname 
from Course  
    inner join CourseInstructor as CI 
        on Instructor.InstructorID=CourseInstructor.InstructorID
        and Course.CourseID = CourseInstructor.CourseID

This is a query I made to inner join many to many table. I have course and instructor table and join table which includes the course and instructorid.

I want to inner join the tables to display the courses taught by each instructor but getting an error:

multipart identifier could not be bound

.. for instructor.name, instructor.insturctorid, courseinstructor.instructorid and courseinstructor.courseid.

CodePudding user response：

The error basically states that you are referencing a table called Instructor that the query cannot find in the query itself. Your SELLECT does reference a table called Instructor, but the CourseInstructor table is subsequently aliased as CI:

inner join CourseInstructor as CI

Change that to

inner join CourseInstructor as Instructor

CodePudding user response：

you need to use everywhere in the SELECT and in the ON clause the same table names or aliases that are defined in the FROM clause

So your query mus look like this

select Course.CourseName as course_coursename
,Instructor.Name as 
instructor_instructorname 
from Course  inner join 
CourseInstructor as Instructor on Course.InstructorID = Instructor.InstructorID
AND Course.CourseID = Instructor.CourseID