Give an insert in a table with information from two tables

Question

Give an insert in a table with information from two tables

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I would like to know how to insert two ids into two tables. For example, there are 5 tables one is a 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 subject table you have the following information:

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

The pupil_turma table that the student together with the class is:

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

In the table, which makes the joining 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 binds the class material. Example

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

mysql sql sql-server

asked by anonymous 03.05.2018 / 15:11

2 answers

1

You need to make a select by joining together 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;

03.05.2018 / 15:19

JQuery code presents different result depending on browser Problem with JS / PHP [closed]

score 1 · Accepted Answer

You could remove the student_turma table and put a column with the class name in 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;