Consulta SQL - JOIN - A Y U D A !!!

[deivisjoro]

Buenas tardes.
La presente es para pedir ayuda frente a una consulta que he tratado de realizar durante todo el dia y no he logrado obtener el resultado esperado.

Estoy desarrollando una aplicación para la administración de las notas de una institución educativa, tratando de realizar un reporte llamado "Boletin", el cual debe mostrar el rendimiento de los estudiantes frente a las asignaturas que se ven en el grado que se encuentra matriculado, tengo una consulta que funciona, pero se que mas adelante se presentaran problemas, puesto que la consulta actual solo muestra las asignaturas de un estudiante, cuando estas tienen una nota asignada, si el docente encargado de la asignatura no ha "subido" sus calificaciones, estas asignaturas no aparecerán en el reporte, he tratado de hacerlo con LEFT JOIN, pero hay algo que no esta funcionando.

Muestro la estructura de las tablas con las cuales estoy trabajando

Tablas Asignaturas

Código:

CREATE TABLE IF NOT EXISTS `asignaturas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `descripcion` varchar(100) NOT NULL,
  `area_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=68 ;

Tabla Asignaturas por Grado

Código:

CREATE TABLE IF NOT EXISTS `asignaturas_grados` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `asignatura_id` int(11) NOT NULL,
  `grado_id` int(11) NOT NULL,
  `intensidad` int(11) NOT NULL,
  `porcentaje` double(11,0) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=134 ;

Tabla Notas

Código:

CREATE TABLE IF NOT EXISTS `notas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `estudiante_id` int(11) NOT NULL,
  `periodo_id` int(11) NOT NULL,
  `asignatura_id` int(11) NOT NULL,
  `grupo_id` int(11) NOT NULL,
  `nota` double NOT NULL,
  `fallas` int(11) NOT NULL,
  `observaciones` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2120 ;

Voy a dejar de lado las consultas anteriores que he estado tratando de hacer y muestro una que se puede acercar a los resultados esperados:

Notas del estudiante 1275 en el grado 9

Código:

SELECT Estudiante.apellido1, Estudiante.nombre1, Asignatura.descripcion
FROM personas AS Estudiante, asignaturas_grados AS AsignaturaGrado, asignaturas AS Asignatura
WHERE Estudiante.id = 1275
AND AsignaturaGrado.asignatura_id = Asignatura.id 
AND AsignaturaGrado.grado_id = 9

Este estudiante debe estar asistiendo a 14 clases, pero solamente 3 de ellas tienen nota asignada, como puedo hacer para que siga mostrando estas mismas 14 asignaturas, 11 con el valor NULL y las 3 con sus respectivas notas

[gnzsoloyo]

Cuando usas LEFT JOIN no puedes usar al mismo tiempo la coma (JOIN implícito). Debes en todo caso usar INNER JOIN para ese caso.
Pero también tienes algún problema de diseño porque no se ve la relación entre los estudiantes y las asignaturas.
¿Cómo se vinculan?
¿Hay alguna tabla que registro que un alumno determinado está inscripto para cursar una asignatura, o cómo administras eso?

Sin relacionar las asignaturas al alumno, no puedes relacionar las notas con la asignatura, o al menos el modelo tendría deficiencias...

Hay algunas cosas oscuras en ese modelo de datos.

[deivisjoro]

Hola gnzsoloyo, de nuevo.

las tablas que publique en el post son las que se necesitan para la consulta, no es el modelo completo, pues el original tiene 56 tablas.

los estudiantes estan relacionados con una tabla llamada "matriculas" donde se registra el codigo del estudiante y el codigo del grupo, lo grupos pertenecen a unos grados y para cada grado se dice que asignaturas deben cursarse en dicho grado, esta informacion esta relacionada en la tabla "asignaturas_grados"

[deivisjoro]

Me respondo:

Código:

SELECT Estudiante.id, Estudiante.apellido1, Estudiante.apellido2, Estudiante.nombre1, Estudiante.nombre2, 
                       Area.id, Area.descripcion, Asignatura.descripcion, Asignatura.id, Nota.* 
                FROM matriculas AS Matricula, grupos AS Grupo, personas AS Estudiante  
                JOIN asignaturas_grados AS AsignaturaGrado  
                JOIN asignaturas AS Asignatura ON AsignaturaGrado.asignatura_id = Asignatura.id
                JOIN areas AS Area ON Asignatura.area_id = Area.id 
                LEFT JOIN notas AS Nota ON Asignatura.id = Nota.asignatura_id 
                     AND Nota.estudiante_id = Estudiante.id 
                     AND Nota.periodo_id = 4
                WHERE AsignaturaGrado.grado_id = 9
                AND Estudiante.id = Matricula.persona_id
                AND Matricula.grupo_id = Grupo.id
                AND Grupo.id = 32
                ORDER BY  Estudiante.apellido1, Estudiante.apellido2, Estudiante.nombre1, Estudiante.nombre2, Area.descripcion, Asignatura.descripcion

[gnzsoloyo]

En términos generales, la consulta que planteas esta mas o menos correcta. Hay algunas cosas que se pueden mejorar, pero que no afectan a la lógica de la consulta:

- No uses coma, si usas JOIN, LEFT JOIN, INNER JOIN o RIGHT JOIN. Pueden producirse errores de interpretación en el parser en ocasiones. A veces incluso saltan errores de sintaxis que no son tales, porque si hay un JOIN de cualquier tpo, la coma genera problemas.

- Pon las tablas en el orden de la cadena de relaciones. Hace más eficiente la lectura de los datos.

- Si usas relaciones de FK, nunca uses MyISAM, las tablas MyISAM no tienen restricciones de FK, por lo que deberás mantener la consistencia de forma programática. Las tablas MyISAM son eficientes sólo si hay muchas operaciones INSERT/UPDATE/DELETE. Y por muchas, estamos hablando de cientos de miles por día, para que quede claro.

- Usa alias abreviados. Poner alias largos es un despropósito, no ayuda a la comprensión del código.

- Los filtros de valores en el FROM (A = N siendo N una constante), no son eficientes si no hay indices definidos para esos campos. En ese caso suele ser mejor ponerlos en el WHERE.

Limpiando un poco el código, queda mas o menos así (es basicamente lo que pusiste):

Código MySQL:

SELECT 
    E.id, 
    E.apellido1, 
    E.apellido2, 
    E.nombre1, 
    E.nombre2, 
    AR.id, 
    AR.descripcion, 
    A.descripcion, 
    A.id, 
    N.* 
FROM 
    personas E 
    INNER JOIN matriculas M             ON E.id = M.persona_id
    INNER JOIN grupos G                 ON G.id = M.grupo_id 
    INNER JOIN asignaturas_grados AG    ON G.id = AG.grado_id 
    INNER JOIN asignaturas A            ON A.id = AG.asignatura_id 
    INNER JOIN areas AR                 ON A.id = A.area_id 
    LEFT JOIN notas N                   ON E.id = N.estudiante_id
WHERE 
    N.periodo_id = 4
    AND AG.grado_id = 9
    AND G.id = 32
ORDER BY  
    E.apellido1, 
    E.apellido2, 
    E.nombre1, 
    E.nombre2;
 

[deivisjoro]

Gracias gnzsoloyo, por tu tiempo.