Problem with joins between students subjects database
I am studying the types of joins and performing exercises, I can do without problem the inner joins but, I have difficulty with the other variants like the left join, in my example I have created a small and simple database.
Data entered in the tables:
MATERIA1
IDMATERIA1 | MATERIA1
-----------+-------------
1 | Ingles
2 | Programacion
3 | Matemáticas
4 | Literatura
5 | Algebra
CARRERA1_MATERIA1
ID | IDCARRERA1 | IDMATERIA1
----------+------------+-----------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 2 | 1
5 | 2 | 3
6 | 2 | 4
7 | 3 | 1
8 | 3 | 3
9 | 3 | 4
10 | 2 | 5
CARRERA1
IDCARRERA1 | CARRERA1
-----------+---------------
1 | Sistemas
2 | Administracion
3 | Derecho
ALUMNO1
IDALUMNO1 | IDCARRERA1 | ALUMNO1
----------+------------+------------
1 | 1 | Jean Carlos
2 | 2 | Maria
3 | 3 | Carla
Originally the exercise consists of finding out the career and the subjects that the student has = Jean Carlos
This is the code I used to join the tables and get what I wanted, with that I already have the subjects that the student is taking:
select a.IDALUMNO1, a.ALUMNO1, m.MATERIA1
from ALUMNO1 a
inner join CARRERA1 c on a.IDCARRERA1 = c.IDCARRERA1
inner join CARRERA1_MATERIA1 cm on cm.IDCARRERA1 = c.IDCARRERA1
inner join MATERIA1 m on m.IDMATERIA1 = cm.IDMATERIA1
where ALUMNO1='Jean Carlos'
Now I need to know what the subjects that he is not studying, according to my records he is not studying neither literature nor algebra, supposedly by changing the inner for a left join I was going to show the subjects that have no relation to my student, but it does not work, it continues to appear the same.
Where is the problem and how can I fix it?
1 answers
Answering my own question the solution I found was as follows:
select a.alumno1, m.materia1
from materia1 m
left join carrera1_materia1 cm on m.idmateria1 = cm.idmateria1
left join carrera1 c on c.idcarrera1 = cm.idcarrera1
left join alumno1 a on a.idalumno1 = c.idcarrera1
where a.idalumno1 is null