How is the join and WHERE with multiple tables made?

Navigation
3

I have 4 tables they already have foreign key. 

aluno:
id
matricula
username
curso_id          // referencia a id_cursos da tabela cursos, coluna id_cursos.

cursos: 
id_cursos
nome_cursos

modulos:
id_modulos
nome_modulos
cursos_id         //faz referencia a tabela cursos, coluna id_cursos.

materias:
id_materias
nome_materias
modulos_id       // faz referencia a tabela modulos, coluna id_modulos.

How do I join to return the related columns of these tables? if I run the code: 

SELECT cursos.nome_curso, aluno.username 
FROM aluno JOIN cursos on cursos.id_cursos = aluno.curso_id;

will return only the relationship of these two tables, but the 4 are related how would I do this?

    
asked by anonymous 04.11.2017 / 14:57

3 answers

2

Just add the other relationships in Join 

SELECT C.nome_curso, A.username, M.nome_modulos, MA.nome_materias FROM aluno A
JOIN cursos C on C.id_cursos = A.curso_id
JOIN modulo M on M.id_modulos = C.id_cursos
JOIN materias MA on MA.id_materias = M.modulos_id

If you have a relationship that does not necessarily have to be filled, for example, materials, use LEFT JOIN

    
04.11.2017 / 15:07
1

Just use the same logic as for a simple JOIN, only using LEFT OUTER JOIN 

Select A.id, A.matricula, A.username, A.curso_id, B.id_cursos, B.nome_cursos, C.id_modulos, C.nome_modulos, C.cursos_id, D.id_materiais, D.nome_materiais, D.modulos_id 
FROM Aluno A LEFT OUTER JOIN Cursos B ON (A.curso_id = B.id_cursos),
LEFT OUTER JOIN Modulos C ON (B.id_cursos = C.cursos_id),
LEFT OUTER JOIN Materias D ON (C.id_modulos = D.modulos_id)
    
04.11.2017 / 15:27
1

Just as I add information from Thiago Loureiro, by selecting LEFT JOIN these to choose all the register that you have the left and only those (the right) that are found. The remaining (Left) appear in the same but without the data from the right table.

If you do not do this, when the link is not found the left register is not shown, being "lost"

    
06.11.2017 / 18:45
Problem with .find () Convert Char to string in C # [duplicate]