Problem with joins among database students subjects

Question

Problem with joins among database students subjects

Navigation

#1 by (3 votes)

3

I am studying the types of joins and doing exercises, I can do the inner joins without problems, 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:

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 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 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 what are the subjects that are NOT studying, according to my records is not studying either literature or algebra, supposedly changing the inner for a left join would show subjects that have no relationship with my student, but it does not work, the same thing keeps appearing.

Where is the problem and how can I fix it?

mysql sql join

asked by jeancarlos733 13.07.2016 в 05:03

source

1 answer

Error in PHP sqlsrv_num_rows () Is there something in swift like the python raw_input?

score 3 · Accepted Answer

Responding to my own question, the solution I found was the following:

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