I am studying the types of joins and doing exercises, I manage to do the inner joins without any problem, but I have difficulty with the other variants such as the left join, in my example I have created a small and simple database.
Data entered in the tables:
SUBJECT1
IDMATERIA1 | MATERIA1
-----------+-------------
1 | Ingles
2 | Programacion
3 | Matemáticas
4 | Literatura
5 | Algebra
CAREER1_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
RACE1
IDCARRERA1 | CARRERA1
-----------+---------------
1 | Sistemas
2 | Administracion
3 | Derecho
STUDENT1
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 that I used to join the tables and obtain what I wanted, with that I already have the subjects that the student is studying:
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 which subjects he is NOT taking, according to my records he is not taking literature or algebra, supposedly by changing the inner for a left join it would show the subjects that are not related to my student, but it does not work the same thing keeps popping up.
Where is the problem and how can I fix it?
Answering my own question the solution I found was the following: