Listado de asignaturas, notas y promedios semestral y anual

Hola, después de pasar casi una semana con esto me decido por fin a comentarlo para solicitar vuestra siempre tan valiosa ayuda. Lo que deseo hacer es un listado de asignaturas con sus calificaciones correspondientes, incluidos los promedios por asignatura y el promedio general. Sería en 3 tablas. 1 para cada semestre y una tercera para poner sólo los promedios.

Las tablas tienen la siguiente estructura:

alumnos -> id_alumno | id_curso | id_apoderado |
asignaturas_curso -> id_curso | id_asignatura |
asignaturas -> id_asigantura | nombre_asignatura |
calificaciones -> id_alumno | id_asignatura | id_semestre | n1 | n2 | n3 | n4 | ... | n15 |

Antes que todo, aclaro que he listado las notas definiendo una columna por cada una debido a que necesito controlar su posición en la tabla, pues a veces quedan notas sin poner y es necesario guardar su posición.

Pensando sólo en listar las asignaturas hago esto:

Código PHP :

<?php
$consulta = "
select alumnos.*, asignaturas_curso.*, asignaturas.*
from alumnos inner join asignaturas_curso inner join asignaturas
on alumnos.apoderado = '".($_SESSION['usuario'])."'
where
alumnos.id_curso = asignaturas_curso.id_curso and
asignaturas_curso.id_asignatura = asignatura.id_asignatura";
$resultado = mysql_query($consulta) or die(mysql_error());
$num_filas = mysql_num_rows($resultado);
while($datos = mysql_fetch_array($resultado)){
?>
<table><tr>
<td><?php echo $datos['nombre_asignatura'] ?></td>
...
<td></td>
</tr></table>
<?php } ?>

Con eso así las asignaturas se listan bellamente en mi tabla. El problema me surge con las notas. Para extraer las notas agrego unas líneas a la consulta y modifico la tabla. Al final queda así:

Código PHP :

<?php
$consulta = "
select alumnos.*, asignaturas_curso.*, asignaturas.*, calificaciones.*
from alumnos inner join asignaturas_curso inner join asignaturas inner join calificaciones
on alumnos.id_apoderado = '".($_SESSION['usuario'])."'
where
alumnos.id_curso = asignaturas_curso.id_curso and 
asignaturas_curso.id_asignatura = asignaturas.id_asignatura and
asignaturas_curso.id_asignatura = calificaciones.id_asignatura and
calificaciones.id_alumno = alumnos.id_alumno and
calificaciones.id_semestre = 1 ";
$resultado = mysql_query($consulta) or die(mysql_error());
$num_filas = mysql_num_rows($resultado);
while($datos = mysql_fetch_array($resultado)){ ?>
<table><tr>
<td><?php echo $datos['nombre_asignatura'] ?></td>
<td><?php echo $datos['n1'] ?></td>
<td><?php echo $datos['n2'] ?></td>
...
<td><?php echo $datos['n15'] ?></td>
<td><?php echo $promedio_asignatura ?></td>
</tr></table> 
<?php } ?>

Al hacer esto tengo varios problemas:

1. En la segunda tabla especifico que el semestre es = a 2 en la consulta. Como no hay notas no hay registros y entonces ya no me muestra la lista de las asignaturas. ¿Sería posible listar de todas formas las asignaturas aunque no tengan notas? Para esto se intenté una segunda consulta dentro del while sólo para rescatar las notas pero no me muestra las notas, así que lo descarté.

2. Para sumar las notas terminé haciéndolo así: $datos['n1']+$datos['n2']+...$datos['n15'] y para calcular el número por el cual dividir usé if... else para determinar si el valor era mayor que 0. En caso de que sí, le asigno 1 y si no le asigno 0 y después sumo. Al final saco el promedio redondeando con round, pero después de extensas líneas de código y me imagino que debe haber una forma más simple de hacerlo.

3. Después de muchas líneas de código repetido ya tengo mis promedios por asignaturas, pero ahora tengo que sumar esos promedios generados en el while, para el promedio general del semestre. El problema es que no he logrado sumar los valores de $promedio_asignatura en todas las filas generadas. Con num_filas ya sé por cuál dividir, pero me falta la suma.

Como mencioné al principio, con este código pretendo mostrar 1 tabla por semestre, cambiando en cada una el id del semestre a mostrar, más una tercera tabla donde quiero rescatar los promedios de las tablas anteriores. El problema es que como los promedios han salido de dos bucles distintos no sé cómo hacer para traerlos a esta tercera tabla sin tener que hacer una nueva consulta.

Eso sería. Espero cualquier ayuda. Tengo más que claro que es posible hacerlo más simple pero a estas alturas el cansancio mental ya me ha ganado. Agradecido de antemano,

Buena Caza y Largas Lunas.

1. En lugar de INNER JOIN, usa LEFT JOIN o RIGHT JOIN dependiendo donde en que posicion este la tabla.

2.3. Hay funciones como SUM, AVG que en conjunto con GROUP BY pueden ayudarte en temas de promedio y sumatorias.

...

Tu query deberia ser mas ANSI SQL

Código MySQL :

select alumnos.*, asignaturas_curso.*, asignaturas.*
from alumnos 
left join asignaturas_curso 
     on alumnos.[id_comun] = asignaturas_curso.[id_comun]
left join asignaturas
     on asignaturas_curso.[id_comun] = asignaturas.[id_comun]
WHERE
 alumnos.apoderado = 'perez'

Gracias edge por contestar. No había respondido porque estaba haciendo pruebas y leyendo según tu recomendación. En el siguiente script he usado alias y, según lo que entendí, puse entre ( ) la parte de la consulta que tiene prioridad y que es la encargada de mostrar las asignaturas. A continuación he usado left join para las notas.

Código PHP :

select
s.nom_asignatura,
n.nota1,n.nota2,n.nota3,n.nota4,n.nota5,n.nota6,n.nota7,n.nota8,n.nota9,n.nota10,n.nota11,n.nota12,n.nota13,n.nota14,n.nota15,
nota1+nota2+nota3+nota4+nota5+nota6+nota7+nota8+nota9+nota10+nota11+nota12+nota13+nota14+nota15 as suma_as
from
(
r_alumnos a inner join r_asignaturas_curso c on a.id_curso = c.id_curso
inner join s_asignaturas s on c.id_asignatura = s.id_asignatura
)
left join r_calificaciones n 
on c.id_asignatura = n.id_asignatura and 
n.id_division = 21 and 
n.id_alumno = a.id_alumno
where
a.id_apoderado = '".($_SESSION['usuario'])."'
order by asig_lista asc

Y ahora si cambio el id_division = 21 por id_division = 22 me lista las asignaturas aún cuando no existan notas en la bd.



Con los datos que obtengo sumo las notas de cada asignatura en suma_as y las divido por la cantidad de notas (esto lo calculo en php sumando la cantidad de notas de la 1 a la 15 distintas de 0) y sacando el promedio el que almaceno en la variable $prom_as.

Código PHP :

$prom_as = round($suma_as/$divisor)

De este modo, el while en cada vuelta me muestra el promedio en su celda respectiva. El problema que sigo teniendo es el de cómo sumar todos los resultados de $prom_as generados en el bucle. Esa suma es lo único que me falta para poder calcular el promedio general del semestre.

Eso lo repetiré por cada semestre. Ahora bien, no sé qué sería más recomendable:

a) Almacenar de alguna manera cada resultado de $prom_as en una variable por asignatura y por semestre para sacar los anuales, o
b) Repetir las dos consultas en la tabla de los anuales y de ahí sacar los resultados.

Sigo atento a cualquier comentario.

Buena Caza y Largas Lunas.

Y bueno, después de seguir buscando para el tema del promedio general, al final di con la solución. Es tan simple que me dio vergüenza.

Lo que quería sumar eran los promedios de cada asignatura, los que eran generados en cada iteración de bucle y almacenados en $prom_as. Era esta última variable la que no sabía como sumar. Al final la solución es la siguiente: antes de iniciar el bucle incializo la variable $suma_prom = 0 y luego, dentro del bucle y después de las notas y sus promedios lo siguiente:

Código PHP :

$suma_prom+=$prom_as; $prom_s = round($suma_prom/$num_as); }

Y listo. Ya tengo mi promedio general.



Ahora sólo me queda definir la forma de mostrar los anuales. Se me ocurre que una forma de hacerlo sería recuperar las variables de las consulas anteriores y meterlas en la tabla de los anuales. La otra opción sería hacer nuevamente una consulta y sacar los promedios. Para esto último he realizado la misma consulta que para los semestres, sólo que sin especificar el id_division, esto para después en cada columna mostrar los promedios que sí correspondan a cada semestre. Sin embargo, aún no logro que de la misma consulta me muestre resultados filtrados por id_division. Probé con if... else if..., con switch..., group by id_division, y en el caso de éste último la consulta me arrojaba error.

¿Alguna sugerencia que me ilumine para seguir investigando y probando?

Buena Caza y Largas Lunas.