Sub Query inside a JOIN statement

Databases
1

Hello to all,
I have been battling with this for about 6 hours straight. Hoping someone here can help.
Here is the situation,

I have a table, called scheduledcourses. This table stores course information.
The application offers these courses to instructors to teach, and instructors have to accept the course electronically (accept or deny).
This action is stored in a table instructor_acceptance_history and in this table it tracks the instructor id, the status, and the course id.

I need a query that pulls all the course information, AS WELL AS theinstructor_acceptance_history related to the course id in question, however i only need to see the record inside instructor_acceptance_history with the highest value (highest id) because the latest entry is the only one that matters.

My query is long but i am posting it here so I donmt leave anything out.

In summary, the line that should be a sub query is


LEFT JOIN instructor_acceptance_history ON scheduledcourses.scheduledcoursesid = instructor_acceptance_history.courseid

I need it to select the record on that table that has the highest value in the id column


SELECT
scheduledcourses.scheduledcoursesid,
scheduledcourses.key_course,
scheduledcourses.courseparent,
scheduledcourses.coursecost,
scheduledcourses.instructorfee,
scheduledcourses.notes,
scheduledcourses.instructornotestoadmin,
scheduledcourses.notestoinstructor,
scheduledcourses.adminnotes,
scheduledcourses.coursedate,
scheduledcourses.coursetime,
scheduledcourses.courseendtime,
scheduledcourses.courseroster,
scheduledcourses.coursenumberofseats,
scheduledcourses.coursestatus,
scheduledcourses.coursetype,
scheduledcourses.privatecourse,
scheduledcourses.courseinstructor,
coursetypes.coursetypesid,
coursetypes.coursetypename,
coursetypes.comments,
coursetypes.coursetypecert,
locations.locationsid,
locations.locationname,
locations.locationcontact,
locations.locationaddress,
locations.locationphone,
locations.locationaddress2,
locations.locationcity,
locations.locationstate,
locations.locationzip,
locations.locationnotes,
instructors.instructorname,
instructors.instructorlastname,
instructors.instructorsid,
instructors.instructoremail,
instructor_cert_agency.inst_cert_agency_name,
trainingsites.trainingsitename,
corp_clients.clientname,
scheduledcourses.av_display_type,
scheduledcourses.coursetype_options,
scheduledcourses.av_provided_by,
instructor_acceptance_history.`status`
FROM
scheduledcourses
LEFT JOIN coursetypes ON (scheduledcourses.coursetype = coursetypes.coursetypesid)
LEFT JOIN locations ON (scheduledcourses.courselocationid = locations.locationsid)
LEFT JOIN instructors ON (scheduledcourses.courseinstructor = instructors.instructorsid)
LEFT JOIN instructor_cert_agency ON coursetypes.coursetypecert = instructor_cert_agency.inst_cert_agencyid
LEFT JOIN trainingsites ON scheduledcourses.courseparent = trainingsites.trainingsiteid
LEFT JOIN corp_clients ON scheduledcourses.coursecorpparent = corp_clients.clientid
LEFT JOIN instructor_acceptance_history ON scheduledcourses.scheduledcoursesid = instructor_acceptance_history.courseid
WHERE scheduledcourses.scheduledcoursesid = $courseid
2

replace this –


LEFT JOIN instructor_acceptance_history 
ON scheduledcourses.scheduledcoursesid = instructor_acceptance_history.courseid

with this –


LEFT JOIN ( SELECT courseid
                 , MAX(id) AS latest
              FROM instructor_acceptance_history
            GROUP
                BY courseid ) AS m
ON m.courseid = scheduledcourses.scheduledcoursesid              
LEFT JOIN instructor_acceptance_history 
ON instructor_acceptance_history.courseid = m.courseid
AND instructor_acceptance_history.id = m.latest