SELECT
USER.firstname AS Firstname,
USER.lastname AS Lastname,
USER.idnumber Employee_ID,
course.fullname AS Course
FROM prefix_course AS course
JOIN prefix_enrol AS en ON en.courseid = course.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_user AS USER ON USER.id = ue.userid
WHERE USER.id NOT IN (
SELECT u.id
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_role AS r ON r.id = ra.roleid
JOIN prefix_user AS u ON u.id = ra.userid
WHERE c.id=course.id
)
ORDER BY Course, Lastname, Firstname
I am trying to use the following SQL to pull the Course ID Number without success"
c.idnumber AS CourseIDNum
after this section:
USER.firstname AS Firstname,
USER.lastname AS Lastname,
USER.idnumber Employee_ID,
course.fullname AS Course
c.idnumber AS CourseIDNumBut I get a message saying error.
What am I missing?