How to make relationship between 5 tables?

Question

I have the following tables: conteúdo, aulas, curso, turma, grupo_User where logic will be the following:

1. Content can belong to several classes, as a class can have several contents
2. A class can belong to several courses, as well as a course can have several classes.
3. A course can belong to several classes, as well as a class can have several courses to enroll.
4. A group of users can be linked to several classes, as well as a class can receive several different user groups.

If I’m right, those relationships are all N:N, I also know that an extra table is needed to make the link between the tables.

Below I will leave the structure of the tables:

Content:

CREATE TABLE conteudo (
    id INT(12) PRIMARY KEY,
    autor VARCHAR(50),
    conteudo MEDIUMTEXT,
    data_criacao DATETIME,
    ultima_edicao VARCHAR(50),
    data_edicao DATETIME
);

Lessons:

CREATE TABLE aulas (
    id INT(12) PRIMARY KEY,
    autor VARCHAR(50),
    nome VARCHAR(50),
    data_criacao DATETIME,
    ultima_edicao VARCHAR(50),
    data_edicao DATETIME
);

Course:

CREATE TABLE curso (
    id INT(12) PRIMARY KEY,
    autor VARCHAR(50),
    nome VARCHAR(50),
    data_criacao DATETIME,
    ultima_edicao VARCHAR(50),
    data_edicao DATETIME
)

Classes:

CREATE TABLE turmas(
    id INT(12) PRIMARY KEY,
    autor VARCHAR(50),
    nome VARCHAR(50),
    data_criacao DATETIME,
    ultima_edicao VARCHAR(50),
    data_edicao DATETIME
)

Group_user:

CREATE TABLE grupo_User(
    id INT(12) PRIMARY KEY,   
    nome_Grupo VARCHAR(50),
    id_Users INT(20)
)

I even managed to link the content to the lessons using the following table:

CREATE TABLE aula_has_conteudo (
    idAula INT(12),
    idConteudo INT(12),
    CONSTRAINT PK_aulas_has_conteudo PRIMARY KEY (idAula, idConteudo),
    CONSTRAINT FK_aulas_has_conteudo_aula FOREIGN KEY (idAula) REFERENCES aulas (id),
    CONSTRAINT FK_aulas_has_conteudo_conteudo FOREIGN KEY (idConteudo) REFERENCES conteudo (id)
);

Using the following SELECT can bring the desired data:

SELECT  a.autor as autorAula, c.conteudo as conteudo FROM aulas a INNER JOIN aula_has_conteudo h ON a.id = h.idAula 
INNER JOIN conteudo c ON c.id = h.idConteudo

And I got the aulas with the cursos using the following table:

CREATE TABLE curso_has_aula (
    idCurso INT(12),
    idAula INT(12),
    CONSTRAINT PK_curso_has_aula PRIMARY KEY (idCurso, idAula),
    CONSTRAINT FK_curso_has_aula_Curso FOREIGN KEY (idCurso) REFERENCES curso (id),
    CONSTRAINT FK_curso_has_aula_Aula FOREIGN KEY (idAula) REFERENCES aulas (id)
)

But I can’t make the connection between the other tables.

How can I relate all these tables knowing the requirements mentioned above?

After related, how can I make a select to obtain data from the five tables?

Answer 1

Just keep doing the joins with the other tables, it will get big but just go adding the other tables:

SELECT a.autor as autorAula, c.conteudo as conteudo 
  FROM aulas a 
 INNER JOIN aula_has_conteudo h ON a.id = h.idAula 
 INNER JOIN conteudo c ON c.id = h.idConteudo
 INNER JOIN curso_has_aula cha on a.id = cha.idAula
 INNER JOIN curso cr on cr.id = cha.idCurso
 -- e assim por diante