Search multiple tables at the same time Mysql

Question

Well, I’ve got the tables professores, materias, turmas. To make it easier and simpler (because all I need is that data), say that there are the following data:

turmas (2)
+-------+----------+---------+
|  id   |  codigo  |  nome   |
+-------+----------+---------+
|  10   |     5    | TURMA X |
|  20   |     6    | TURMA Y |
+-------+----------+---------+

materias (3)
+-------+-----------+---------+
|  id   |   nome    |  turma  |
+-------+-----------+---------+
|  123  | MATERIA X |    5    |
|  124  | MATERIA Y |    5    |
|  125  | MATERIA Z |    6    |
+-------+-----------+---------+

professor (1)
+-------+-----------+----------+
|  id   |   nome    | materias |
+-------+-----------+----------+
|   1   |  Carlos   | 123, 125 |
+-------+-----------+----------+

I need to create a SELECT that takes the CODE and NAME data from the CLASSES table based on what is inserted in MATERIALS from the PROFESSOR table. These materials are separated by commas. Therefore, the query must pass through these 3 tables, taking the information from professor of materials., searching separated by a comma materias.class, to then reach the class code and name.classes.

It’s been a long time since I’ve programmed, anyone who can help, I appreciate!

Answer 1

As the tables are described, they are not formatted in a normalized form.

I think the ideal was to make a table of relationship, that linked Professor to Matter and another linking the class to matter.

So In addition to the tables you have, you would have a new Professor_materia table, which would contain two fields:

The Id_professor and the Id_materia.

and another new table turma_materia, which would contain the fields:

The id_materia and the Id_turma.

Remove the material field from the Teacher table

Remove from table Materia the class field

The tables would look like this:

turmas (2)
+-------+----------+---------+
|  id   |  codigo  |  nome   |
+-------+----------+---------+
|  10   |     5    | TURMA X |
|  20   |     6    | TURMA Y |
+-------+----------+---------+

materias (3)
+-------+-----------+
|  id   |   nome    |
+-------+-----------+
|  123  | MATERIA X |
|  124  | MATERIA Y |
|  125  | MATERIA Z |
+-------+-----------+

professor (1)
+-------+-----------+
|  id   |   nome    |
+-------+-----------+
|   1   |  Carlos   |
+-------+-----------+

Materia_Professor(2)
+--------------+----------+
|  id_professor|id_materia|
+--------------+----------+
|   1          |    123   |
+--------------+----------+
|   1          |    125   |
+--------------+----------+

turma_materia(3)
+-------------+----------------+
|codigo_turma |  id_materia    |
+-------------+----------------+
|   5         |     123        |
+-------------+----------------+
|   5         |     124        |
+-------------+----------------+
|   6         |     125        |
+-------------+----------------+

So you would make a select by making joins with the tables teacher, class, materia and the new tables created. getting like this:

SELECT T.CODIGO, T.NOME
FROM TURMA  T, MATERIA M, PROFESSOR P, MATERIA_PROFESSOR MP, TURMA_MATERIA TM
WHERE 
M.ID= TM.ID_MATERIA AND 
T.CODIGO = TM.CODIGO_TURMA  AND
MP.Id_Materia = M.ID AND
MP.Id_Professor  = P.ID;

Answer 2

Hello,

If you need to search in the tables as they are can do as follows:

select t.codigo,t.nome
from turma t
inner join materias m on t.codigo = m.turma
inner join professor p on m.id in(p.materias)

This query only brings the information that is found, if you only need the class information you can use a left Join