Foro de elhacker.net

Hola gente tengo un problema con una consulta que quiero hacer y no me sale

tengo las siguientes tablas

alumno
-nombre
-apellido
id_alumno
id_curso

curso
-materia
-id_curso

nota
-nota
id_nota
id_alumno

y lo que quier hacer es listar los alumnos por materia y mostrar  el promedio mayor por materia

Alguna idea como hacerlo

logre hacer esto

SELECT a.nombre AS alumno,avg(n.nota)AS promedio
FROM alumno AS a
INNER JOIN curso AS c ON(c.id_curso=a.id_curso)
INNER JOIN nota AS n ON(n.id_alumno=a.id_alumno)
WHERE a.id_alumno IN (SELECT a2.id_alumno FROM alumno AS a2 
INNER JOIN nota AS n
ON (n.id_alumno = a2.id_alumno)
GROUP BY a2.id_alumno
HAVING avg (n.nota)>7)
GROUP BY a.nombre

pero me muestra 1 alumno de 1 materia y su promedio
alguna idea?
gracias

Si es que te entendí bien eso solo lo hacés con dos consultas, ¿ por qué ? porque en la primera parte: "mostrar los alumnos por materia" mostras muchos alumnos por materia, en la segunda: "el promedio mayor por materia" es un solo dato por materia, en una tabla no podrías encajar esos datos de esa forma.
Lo que sí podés hacer es algo como: mostrar la cantidad de alumnos por materia y el promedio máximo.