Table Link - Mysql

Question

I’m setting up a system for the user to register/delete/edit/view courses of the school where I work, I set up all the tables but when I will do a inner join in one of the tables does not give me the expected result I would like.

I own the following tables:

• curso (idCurso, curso_nome, curso_descricao, curso_imagem, curso_carga, Preco_idPreco)

• curso_categoria (idCurso_Categoria, Categoria_Curso_idCategoria_Curso, Curso_idCurso)

• categoria_curso (idCategoria_Curso, categoria_curso_nome, categoria_curso_descricao, categoria_curso_image)

• materia (idMateria, materia_nome, materia_conteudo)

• curso_materia (idCurso_Materia, Curso_idCurso, Materia_idMateria)

• preco (idPreco, preco_valor)

These are the tables of my system, my problem is when I do a inner join and try to get information from the associative table curso_materia.

My code

select curso.idCurso, curso.curso_nome, curso.curso_carga, 
preco.preco_valor,
categoria_curso.categoria_curso_nome from curso

inner join preco on preco.idPreco = curso.Preco_idPreco
inner join curso_categoria on curso_categoria.Curso_idCurso = curso.idCurso
inner join categoria_curso on categoria_curso.idCategoria_Curso = curso_categoria.Categoria_Curso_idCategoria_Curso


/*
inner join curso_materia on curso_materia.idCurso_Materia = curso.idCurso
inner join materia on materia.idMateria = curso_materia.Materia_idMateria
*/

Ok! The expected result when I do not add the line that is in the comment is right and the result is this:

Now if I add in my code that line of comment, it would look like this:

Code with the previous comment

And this comes to me.

Okay, let’s go to some remarks that I might not have mentioned earlier...

• I am using Mysql

• Only 2 test subjects have been added (Advanced Concepts and Color Analysis)

• To image 2 I wanted to return twice the part of Colorimetry, but with different subjects, equal to the following image: (edition made in Paint)

  

Table Course

Associative Table: `Curso_materia`

Table Matter

Where am I wrong? Apparently the course table, courses_category and categorie_course have given certain, and are N:N...

PS: My real focus is on the relationship between these tables, the user table itself here I didn’t put on account that it would only give more work to readers (or it would help...).

Answer 1

Apparently the problem is in the first commented Join, where you are doing the Join of curso_materia.idCurso_Materia with curso.idCurso. If this table is from an N:N association, it should be from curso_materia.Curso_idCurso with curso.idCurso. Thus:

inner join curso_materia on curso_materia.Curso_idCurso = curso.idCurso