Recuperar lista con campo que sea un SELECT de otra tabla

[Seixas]

Hola a todos,

quería pedir ayuda o consejo sobre una sentencia SQL bastante complicada que me ha tocado construir y realmente no sé cómo resolverla. El caso real es bastante lioso y usa muchas tablas, así que expongo una versión simplificada.

Las tablas que tengo son:

Alumnos (nif, nombre)
Pruebas (id, asignatura)
Examenes (alumno, prueba, evaluado, nota) donde alumno y prueba son claves foráneas

Es por tanto, una relación N:M donde Examenes es la tabla de relación.

Pues bien, lo que quiero hacer es una consulta que me devuelva todos los estudiantes con su nif, nombre y un campo con el número de exámenes que tiene pendientes de ser evaluados (evaluado a FALSE).

La consulta que hasta ahora he construido es esta:

SELECT Al.*, (SELECT COUNT(prueba) FROM Examenes WHERE evaluado = FALSE AND alumno = Al.nif) AS pendientes FROM Alumnos Al;

El problema que me encuentro es que dentro de la subconsulta para el campo "pendientes" no sabe identificar para cada uno de los alumnos de la tabla, es decir, Al.nif no lo entiende. ¿Hay alguna manera de solventar esto?

¿Alguien lo haría de otra manera?

Mi idea sería conseguirlo sin tener que hacer nuevos campos en la tabla alumno, siempre que fuera posible.

Gracias por cualquier sugerencia que me podais hacer.

[limonchaz]

creo que solo te falta definir la tabla de alumnos dentro de el select de pendientes

[alguienmas]

Iniciado por Seixas

Las tablas que tengo son:

Alumnos (nif, nombre)
Pruebas (id, asignatura)
Examenes (alumno, prueba, evaluado, nota) donde alumno y prueba son claves foráneas

SELECT Al.*, (SELECT COUNT(prueba) FROM Examenes WHERE evaluado = FALSE AND alumno = Al.nif) AS pendientes FROM Alumnos Al;

yo lo hari asi:

SELECT a.nif, a.nombre, count(e.prueba) as pendiente FROM (alumnos as a JOIN examenes as e ON a.nif = e.alumno) WHERE e.evaluado=false;

o asi que es lo mismo:

SELECT a.nif, a.nombre, count(e.prueba) as pendiente FROM alumnos as a, examenes as e WHERE e.evaluado=false AND e.alumno = a.nif;

Espero esten buenas las consultas, pues las hice al vuelo, pero como informacion te dejo estos enlaces de como funcionan los JOIN:

es_wikipedia_org/wiki/Join
dev_mysql_com/doc/refman/5.0/es/join.html

(cambia los _ por . ya que aun no me deja poner enlaces en el foro)

Saludos

[Seixas]

Gracias, antes que nada, por vuestras respuestas.

limonchaz, imagino que te referías a hacer algo así:

Código PHP:

  SELECT Al.*, (SELECT COUNT(prueba) FROM Examenes, Alumnos A WHERE evaluado = FALSE AND alumno = A.nif) AS pendientes FROM Alumnos Al;  
   

He probado lo que me comentas, pero creo que no es la solución. El campo pendientes contiene el mismo valor para todos los alumnos que recupero. Creo que así lo que hace la subconsulta es asignar al valor pendientes todos los examenes que no han sido evaluados.


alguienmas, he probado lo que me comentas, pero tampoco parece funcionar, aunque ha estado cerca. Al cambiar la sentencia me ha devuelto el siguiente error...

Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause'

... pero lo he solventado haciendo un GROUP BY a.nif.
Una vez así, como resultado me duelve todos los alumnos que tienen algún exámen por evaluar, pero yo quiero tener todos los alumnos.

Gracias de nuevo por la ayuda.

¿Alguna otra idea?

[jurena]

No la he probado

Código sql:

SELECT alumnos.nif, alumnos.nombre, t1.total totalpendientes
  FROM alumnos INNER JOIN
    (SELECT alumno, COUNT(*) total FROM examenes
      WHERE evaluado = FALSE GROUP BY alumno HAVING total >=1 )t1
         ON alumnos.nif = t1.alumno ORDER BY alumnos.nombre
 

[Seixas]

jurena, mil gracias. Has dado con la solución.

He tenido que cambiar el INNER JOIN por LEFT JOIN y ha funcionado perfecto.

No sabía que se podían hacer JOINs con subconsultas. Esa era la clave que me faltaba.

Gracias de nuevo. Un saludo a todos.

[jurena]

Es cierto, Seixas,
si querías tener todos los alumnos, tenías que usar el LEFT JOIN, pero cuando salga null, tendrás que usar IFNULL o COALESCE, algo así en el SELECT primero:
select ... IFNULL(t1.total totalpendientes, 'no tiene pendientes')

Saludos.