Give an input in a table with information from two tables

Question

I would like to know how to insert in a table ids of two other tables. For example, there are 5 tables one is student, which has the following information:

+--------+------------+
| ID_ALU | nome       | 
+--------+------------+
|  1     | aluno 1    |
|  2     | aluno 2    |
+--------+------------+

class has the following information:

+--------+------------+
| ID_TUR | nome       | 
+--------+------------+
|  1     | Turma 1    |
|  2     | Turma 2    |
+--------+------------+

In the table materia has the following information:

+--------+------------+
| ID_MAT |  nome      |
+--------+------------+
| 1      | Materia 1  |
| 2      | Materia 2  |
| 3      | Materia 3  |
+--------+------------+

The student table:

+--------+------------+------------+
| ID     | ID_ALU     | ID_TUR     |
+--------+------------+------------+
| 1      |  1         |  2         |
| 2      |  2         |  1         |
+--------+------------+------------+

In the table, that makes the junction of matter with the class is:

+--------+------------+------------+
| ID     | ID_TUR     | ID_MAT     |
+--------+------------+------------+
| 1      |  1         |  2         |
| 2      |  1         |  3         |
| 3      |  2         |  1         |
+--------+------------+------------+

Now I want SQL to check which class is the student and link the class. Example

+-------+---------+---------+
| ID    | ID_ALU  | ID_MAT  |
+-------+---------+---------+
| 1     |  1      |  1      |
| 2     |  2      |  2      |
| 3     |  2      |  3      |
+-------+---------+---------+

Answer 1

You need to make a select by joining all the records and tables you will need, following the relationship, and then making a INSERT. Example:

INSERT aluno_materia
SELECT
    AT.ID_ALU,
    M.ID_MAT
FROM
    materia AS M
    JOIN materia_turma AS MT ON M.ID_MAT = MT.ID_MAT
    JOIN turma AS T ON MT.ID_TUR = T.ID_TUR
    JOIN aluno_turma AS AT ON AT.ID_TUR = T.ID_TUR;

Answer 2

Could remove the student table

| id_alu | nome    | turma
| 1      | aluno 1 | 2
| 2      | aluno 2 | 1

then put the following sql

INSERT NOTA
SELECT
    AL.ID_ALU,
    M.ID_MAT
FROM
    aluno AS AL
    JOIN materia_turma AS MT ON AL.turma = MT.ID_TUR;