How to get all records not in another table with date condition?

Question

MySQL

Good day to all.
There was a problem generating the request. There is a table, it shows the indexes of educational courses, their names and their year. Different courses may take place in different years and the list of courses is updated every year. There is also a table with student enrollments, which indicates the date of enrollment and the number of the course for which he is enrolled. However, it is known that there is a loss of data in the course table (it may have been lost during the transfer or how, that's not the point). But you need to find out which courses are missing. To do this, it is enough to know the course index. It is also known that there are no losses in the crediting table and the data there is correct. So here's the question. How to create a query to select from the enrollment table the numbers of courses that are not in the course table? It would be easier if each course was under the same index every year, then just Right join and that's it. But the point here is that in 2011 there may be one rate under the index 210, and in 2012 there will be another rate for this index. And indexes from all years are just mixed in one table.

Answer 1

If I understood the problem correctly, then something like this

SELECT
  `enrollment_for_courses`.`course_code`,
  YEAR (
    `enrollment_for_courses`.`date`
  ) as course_year,
  `courses`.`code`
FROM
  `enrollment_for_courses`
LEFT JOIN `courses` ON `courses`.`code` = `enrollment_for_courses`.`course_code`
AND `courses`.`year` = YEAR (`enrollment_for_courses`.`date`)
WHERE `courses`.`code` IS NULL
GROUP BY `enrollment_for_courses`.`course_code`, course_year

`enrollment_for_courses` - course enrollment table
`enrollment_for_courses`.`course_code` - course code in the course enrollment table
`enrollment_for_courses`.`date` - course enrollment date
`courses` - courses table
`courses`.`code` - code course to the courses table
`courses`.`year` - the year of the course in the courses table

Answer 2

I would start digging from here

SELECT DISTINCT t1.course_index FROM students_on_courses t1
WHERE t1.course_index NOT IN 
(SELECT t2.index FROM courses t2 )