How to make a NOT IN mysql with 2 tables

Question

How to make a NOT IN mysql with 2 tables

Navigation

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

4

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

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

In the class table_materia has:

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

In the note table, you need to insert the student and the subject linked to the student's class where you do not have the notes table.
In short, you will check if you already have the subject for that student, if not, you will add. Home If it were with a table it would look 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 |

mysql sql

asked by anonymous 05.05.2018 / 15:14

2 answers

4

Doing by parts. We first identify student-note pairs that do not exist in the Note table. There are several ways, 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

There is only one missing INSERT 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 comment, only the student and the subject are inserted into the Note table, the rest assume the default value of the table.

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

05.05.2018 / 17:10

SQL Maximum value of another table Method overload is polymorphism?

score 2 · Accepted Answer

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 4 entities can 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 let's look at the query that retrieves all 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 )

Output:

|    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: link