How to make a NOT IN mysql with 2 tables

Question

I have 3 tables Person, class, Notes, table person has:

+------+--------------+-----------+
| id   |  Nome        |  turma    |
+------+--------------+-----------+
| 1    | Aluno 01     | 1         |
| 2    | Aluno 02     | 2         |

In the table:

+------+--------+---------+
| id   |  turma | materia |
+------+--------+---------+
| 1    |  1     |  1      |
| 2    |  2     |  1      |
| 3    |  2     |  2      |

In the note table I need to insert the student and the subject linked to the student class where there are no grades in the table.
In short, it will check if you already have the subject for that student, if you don’t have it will add.
If it was with a table it would be like this:

SELECT PE.id, PE.nome, TM.materia
FROM pessoa PE
LEFT JOIN turma_materia TM ON PE.turma=TM.turma
WHERE TM.materia NOT IN (SELECT nota_materia FROM nota)

Adding: The note table has

+------+----------+-----------+-------+------+-------+------+
| id   |  Aluno   |  Materia  |  UNI  | UNII | UNIII | UNIV |
+------+----------+-----------+-------+------+-------+------+
| 1    |  1       |   1       |  NULL | NULL | NULL  | NULL |

Answer 1

Doing by parts. First we identify pairs student-notes that do not exist in the Note table. It has several forms, here is a possible alternative:

SELECT PE.id, PE.nome, TM.materia
  FROM pessoa PE
 INNER JOIN turma_materia TM
    ON TM.turma = PE.turma
  LEFT JOIN nota NT
    ON NT.Aluno = PE.Id
   AND NT.Materia = TM.Materia
 WHERE TM.ID IS NULL   -- Combinação Aluno/Materia não existe na tabela Nota

Only the INSERT is missing in the Note table, which can be done like this:

INSERT INTO Nota(Aluno, Materia)
SELECT PE.id, TM.materia
  FROM pessoa PE
 INNER JOIN turma_materia TM
    ON TM.turma = PE.turma
  LEFT JOIN nota NT
    ON NT.Aluno = PE.Id
   AND NT.Materia = TM.Materia
 WHERE TM.ID IS NULL

As detailed in the commentary, only the student and the subject are inserted in the Note table, the rest assume the default value of the table.

Here is the link to the SQL Fiddle. In this example, the Note table only has information related to "Student 01", so the SELECT instruction will return all subjects in which "Student 02" enrolled.

Answer 2

How about changing your database model to simplify your life ?!

I noticed that your problem has 4 distinct entities: Aluno, Materia, Turma and Nota.

These four entities may be represented as follows::

-- MATERIAS
CREATE TABLE tb_materia
(
   id BIGINT PRIMARY KEY,
   descricao TEXT NOT NULL
 );

-- TURMAS
CREATE TABLE tb_turma
(
  id BIGINT PRIMARY KEY,
  descricao TEXT NOT NULL
);

-- ALUNOS
CREATE TABLE tb_aluno
(
  id BIGINT PRIMARY KEY,
  nome TEXT NOT NULL,
  id_turma BIGINT
);

-- NOTAS
CREATE TABLE tb_nota
(
   id_materia BIGINT NOT NULL,
   id_aluno BIGINT NOT NULL,
   UNI REAL,
   UNII REAL,
   UNIII REAL,
   UNIV REAL,
   PRIMARY KEY( id_materia, id_aluno )
 );

Now, let’s go to relationships:

-- ALUNOS -> TURMA (UM aluno possui NENHUMA ou APENAS UMA turma)
ALTER TABLE tb_aluno ADD FOREIGN KEY (id_turma) REFERENCES tb_turma;

-- NOTA -> ALUNO (UMA nota possui obrigatoriamente APENAS UM aluno)
ALTER TABLE tb_nota ADD FOREIGN KEY (id_aluno) REFERENCES tb_aluno;

-- NOTA -> MATERIA (UMA nota possui obrigatoriamente APENAS UMA materia)
ALTER TABLE tb_nota ADD FOREIGN KEY (id_materia) REFERENCES tb_materia;

Registering Data:

-- CADASTRO DAS MATERIAS
INSERT INTO tb_materia ( id, descricao ) VALUES ( 1, 'PORTUGUES' );
INSERT INTO tb_materia ( id, descricao ) VALUES ( 2, 'MATEMATICA' );
INSERT INTO tb_materia ( id, descricao ) VALUES ( 3, 'HISTORIA' );

-- CADASTRO DAS TURMAS
INSERT INTO tb_turma ( id, descricao ) VALUES ( 1, '3-A' );
INSERT INTO tb_turma ( id, descricao ) VALUES ( 2, '2-A' );
INSERT INTO tb_turma ( id, descricao ) VALUES ( 3, '1-B' );

-- CADASTRO DOS ALUNOS
INSERT INTO tb_aluno ( id, nome, id_turma ) VALUES ( 1, 'JOAO', 1 );
INSERT INTO tb_aluno ( id, nome, id_turma ) VALUES ( 2, 'MARIA', 1 );
INSERT INTO tb_aluno ( id, nome, id_turma ) VALUES ( 3, 'JESUS', 2 );

-- CADASTRO DE NOTAS (SOMENTE JESUS do 2-A)
INSERT INTO tb_nota ( id_aluno, id_materia, UNI, UNII, UNIII, UNIV ) VALUES ( 3, 1, 9.0, 8.5, 5.5, 1.4 );
INSERT INTO tb_nota ( id_aluno, id_materia, UNI, UNII, UNIII, UNIV ) VALUES ( 3, 2, 8.5, 4.5, 6.5, 5.2 );
INSERT INTO tb_nota ( id_aluno, id_materia, UNI, UNII, UNIII, UNIV ) VALUES ( 3, 3, 3.0, 9.5, 2.5, 9.4 );

Now we go to the consultation that recovers all the Alunos and Materias independent of Notas registered:

SELECT
    m.descricao AS "Materia",
    a.nome AS "Nome do Aluno",
    t.descricao AS "Turma",
    n.UNI,
    n.UNII,
    n.UNIII,
    n.UNIV
FROM
  tb_aluno AS a
CROSS JOIN
  tb_materia AS m
LEFT JOIN
  tb_nota AS n ON ( n.id_aluno = a.id AND n.id_materia = m.id )
LEFT JOIN
  tb_turma AS t ON ( t.id = a.id_turma )

Exit:

|    Materia | Nome do Aluno | Turma |    uni |   unii |  uniii |   univ |
|------------|---------------|-------|--------|--------|--------|--------|
|  PORTUGUES |          JOAO |   3-A | (null) | (null) | (null) | (null) |
|  PORTUGUES |         MARIA |   3-A | (null) | (null) | (null) | (null) |
|  PORTUGUES |         JESUS |   2-A |      9 |    8.5 |    5.5 |    1.4 |
| MATEMATICA |          JOAO |   3-A | (null) | (null) | (null) | (null) |
| MATEMATICA |         MARIA |   3-A | (null) | (null) | (null) | (null) |
| MATEMATICA |         JESUS |   2-A |    8.5 |    4.5 |    6.5 |    5.2 |
|   HISTORIA |          JOAO |   3-A | (null) | (null) | (null) | (null) |
|   HISTORIA |         MARIA |   3-A | (null) | (null) | (null) | (null) |
|   HISTORIA |         JESUS |   2-A |      3 |    9.5 |    2.5 |    9.4 |

Sqlfiddle: http://sqlfiddle.com/#! 17/cd898