Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Mysql »

Como devolver registros sin valores

 Estas en el tema de Como devolver registros sin valores en el foro de Mysql en Foros del Web. Hola, Tengo 2 tablas y consulta: @import url("http://static.forosdelweb.com/clientscript/vbulletin_css/geshi.css"); Código MySQL: Ver original CREATE TABLE `i _ alumnos` (   `id _ alumno` int ( 11 ...
  #1 (permalink)  
Antiguo 22/02/2013, 17:32
Avatar de sebandrescc2  
Fecha de Ingreso: diciembre-2012
Mensajes: 45
Antigüedad: 11 años, 4 meses
Puntos: 0
Como devolver registros sin valores
Hola,

Tengo 2 tablas y consulta:
Código MySQL: 
Ver original
  1. CREATE TABLE `i_alumnos` (
  2.   `id_alumno` int(11) unsigned NOT NULL AUTO_INCREMENT,
  3.   `id_curso` int(11) NOT NULL,
  4.   `nombre` varchar(100) COLLATE utf8_spanish_ci NOT NULL,
  5.   `apellido` varchar(100) COLLATE utf8_spanish_ci NOT NULL,
  6.   `rut` varchar(10) COLLATE utf8_spanish_ci NOT NULL,
  7.   `direccion` varchar(150) COLLATE utf8_spanish_ci NOT NULL,
  8.   `comuna` varchar(100) COLLATE utf8_spanish_ci NOT NULL,
  9.   `telefono_fijo` int(9) NOT NULL,
  10.   `telefono_mpvil` int(10) NOT NULL,
  11.   `fecha_nac` date NOT NULL,
  12.   `apoderado` varchar(150) COLLATE utf8_spanish_ci NOT NULL,
  13.   `mail_apo` varchar(150) COLLATE utf8_spanish_ci NOT NULL,
  14.   `mail_alum` varchar(150) COLLATE utf8_spanish_ci NOT NULL,
  15.   `comentarios` longtext COLLATE utf8_spanish_ci NOT NULL,
  16.   PRIMARY KEY (`id_alumno`)
  17. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
  18.  
  19. --
  20. -- Volcado de datos para la tabla `i_alumnos`
  21. --
  22.  
  23. INSERT INTO `i_alumnos` (`id_alumno`, `id_curso`, `nombre`, `apellido`, `rut`, `direccion`, `comuna`, `telefono_fijo`, `telefono_mpvil`, `fecha_nac`, `apoderado`, `mail_apo`, `mail_alum`, `comentarios`) VALUES
  24. (1, 1, 'Raquel', 'Castro Galarce', '16024692-0', 'Venus 9511', 'La Florida', 24936613, 78413664, '2003-04-17', 'Sebastián Contreras Castro', '[email protected]', '[email protected]', 'Alumno de prueba'),
  25. (2, 1, 'Sebastián Andrés', 'Contreras Castro', '15449027-2', 'Venus 9511', 'La Florida', 24936613, 78413664, '2003-07-15', 'Raquel Castro Galarce', '[email protected]', '[email protected]', 'Alumno de prueba'),
  26. (3, 1, 'Eduardo Alfredo', 'Morgado Cifuentes', '13558740-k', 'Calle 2 829', 'La Florida', 24565854, 87452165, '2003-08-27', 'Guillermo Barría', '[email protected]', '[email protected]', 'Alumno de prueba'),
  27. (4, 2, 'Matias Ignacio', 'Contreras Carvajal', '18985364-5', 'Huemul 13664', 'La Florida', 46955654, 87854354, '2001-01-20', 'Claudia Carvajal', '[email protected]', '[email protected]', 'Alumno de prueba'),
  28. (5, 2, 'Catalina MIlen', 'Contreras Carvajal', '16845756-3', 'Huemul 13664', 'La Florida', 45654545, 46587212, '2003-02-20', 'Claudia Carvajal', '[email protected]', '[email protected]', 'Alumno de prueba');
  29.  
  30.  
  31. CREATE TABLE `t_notas` (
  32.   `id_nota` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  33.   `id_alumno` int(10) NOT NULL,
  34.   `id_ramo` int(10) NOT NULL,
  35.   `id_curso` int(11) NOT NULL,
  36.   `id_parcial` int(11) NOT NULL,
  37.   `semestre` int(10) NOT NULL,
  38.   `nota` float(2,1) NOT NULL,
  39.   `info` varchar(150) COLLATE utf8_spanish_ci NOT NULL,
  40.   PRIMARY KEY (`id_nota`)
  41. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
  42.  
  43. --
  44. -- Volcado de datos para la tabla `t_notas`
  45. --
  46.  
  47. INSERT INTO `t_notas` (`id_nota`, `id_alumno`, `id_ramo`, `id_curso`, `id_parcial`, `semestre`, `nota`, `info`) VALUES
  48. (0000000001, 1, 1, 1, 1, 1, 5.8, 'Control'),
  49. (0000000003, 3, 1, 1, 1, 1, 7.0, 'Control'),
  50. (0000000004, 1, 1, 1, 2, 1, 3.8, 'Unidad 1'),
  51. (0000000005, 3, 1, 1, 2, 1, 7.0, 'Unidad 1'),
  52. (0000000008, 1, 1, 1, 3, 1, 3.0, 'Unidad 2'),
  53. (0000000009, 3, 1, 1, 3, 1, 6.0, 'Unidad 2'),
  54. (0000000010, 4, 1, 2, 1, 1, 7.0, 'Control 1');
Código MySQL: 
Ver original
  1. SELECT CONCAT(i_alumnos.apellido," ",i_alumnos.nombre)as ALUMNO,
  2. SUM(IF(id_parcial=1,nota,NULL)) AS nota1,
  3. SUM(IF(id_parcial=2,nota,NULL)) AS nota2,
  4. SUM(IF(id_parcial=3,nota,NULL)) AS nota3,
  5.  
  6. /*promedio */
  7. ROUND((sum(nota*(1-abs(sign(id_parcial-1)))) +
  8. sum(nota*(1-abs(sign(id_parcial-2)))) +
  9. sum(nota*(1-abs(sign(id_parcial-3)))))/COUNT(nota),1) as PROMEDIO
  10.  
  11. FROM i_alumnos, t_notas
  12. WHERE i_alumnos.id_alumno = t_notas.id_alumno
  13. AND t_notas.id_curso="1"
  14. AND t_notas.id_ramo="1"
  15. AND t_notas.semestre="1"
  16. GROUP by i_alumnos.apellido
  17. ORDER by i_alumnos.apellido asc

Resumiendo en 1 tabla tengo alumnos y la 2 tabla de calificaciones.

La consulta es para obtener notas parciales por curso y de un ramo en espcifico y me devuelve Nombre | nota 1 |nota 2 | nota 3 | Promedio

El problema de la query es que si un alumno no tiene nota, la consulta lo pasa por alto ya que no tiene registros, porque obviamente no está en la tabla de notas

La idea es que pueda llamar a todos los alumnos de un curso (id_curso=1) tenga o no calificaciones.

Espero que se entienda. Saludos
__________________
Aquí voy a escribir mi firma
Última edición por gnzsoloyo; 22/02/2013 a las 17:57 Razón: Extirpado de referencias externas.
  #2 (permalink)  
Antiguo 22/02/2013, 17:53
Avatar de gnzsoloyo
Moderador criollo
  
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.324
Antigüedad: 16 años, 5 meses
Puntos: 2658
Respuesta: Como devolver registros sin valores
Para eso existe el LEFT JOIN:
Código MySQL: 
Ver original
  1. SELECT 
  2.     CONCAT(A.apellido, ' ', A.nombre) ALUMNO,
  3.     IFNULL(SUM(IF(id_parcial=1, nota, 0)), 0) nota1,
  4.     IFNULL(SUM(IF(id_parcial=2, nota, 0)), 0) nota2,
  5.     IFNULL(SUM(IF(id_parcial=3, nota, 0)), 0) nota3,
  6.     IFNULL(ROUND((sum(nota*(1-abs(sign(id_parcial-1)))) + sum(nota*(1-abs(sign(id_parcial-2)))) + sum(nota*(1-abs(sign(id_parcial-3)))))/COUNT(nota),1), 0) PROMEDIO
  7. FROM 
  8.     i_alumnos A LEFT JOIN t_notas N ON A.id_alumno = N.id_alumno
  9. WHERE 
  10.         N.id_curso=1
  11.     AND N.id_ramo=1
  12.     AND N.semestre=1
  13. GROUP BY A.apellido
  14. ORDER BY A.apellido asc

Tips:
1) AS está obsoleto desde hace años.
2) Si un valor no existe para un cálculo, se pone cero (0), no NULL. NULL destruye cualquier cálculo u operación realizada con ese valor.
3) Las comillas (") no se usan para las cadenas de texto a menos que el server esté específicamente configurado para ello. Se usan apóstrofos (').
4) Los valores numéricos no se ponen entre apóstrofos o comillas.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #3 (permalink)  
Antiguo 22/02/2013, 18:39
 
Fecha de Ingreso: junio-2011
Mensajes: 139
Antigüedad: 12 años, 10 meses
Puntos: 42
Respuesta: Como devolver registros sin valores
Hola sebandrescc2,

Trata con la siguiente sentencia:

Código MySQL: 
Ver original
  1. SELECT
  2.   dev.ALUMNO,
  3.   dev.NOTA1,
  4.   dev.NOTA2,
  5.   dev.NOTA3,
  6.   dev.PROMEDIO
  7. FROM (
  8.   SELECT
  9.     i_alumnos.id_alumno,
  10.     CONCAT(i_alumnos.apellido, ' ', i_alumnos.nombre) ALUMNO,
  11.     SUM(IF(t_notas.id_parcial = 1, t_notas.nota, NULL)) NOTA1,
  12.     SUM(IF(t_notas.id_parcial = 2, t_notas.nota, NULL)) NOTA2,
  13.     SUM(IF(t_notas.id_parcial = 3, t_notas.nota, NULL)) NOTA3,
  14.     ROUND((SUM(t_notas.nota * (1 - ABS(SIGN(t_notas.id_parcial - 1)))) +
  15.     SUM(t_notas.nota * (1 - ABS(SIGN(t_notas.id_parcial - 2)))) +
  16.     SUM(t_notas.nota * (1 - ABS(SIGN(t_notas.id_parcial - 3))))) / COUNT(t_notas.nota), 1) PROMEDIO
  17.   FROM i_alumnos
  18.     LEFT JOIN t_notas ON i_alumnos.id_alumno = t_notas.id_alumno
  19.       AND t_notas.id_curso = '1'
  20.       AND t_notas.id_ramo = '1'
  21.       AND t_notas.semestre = '1'
  22.   GROUP by i_alumnos.id_alumno, ALUMNO
  23.   ORDER by ALUMNO) dev;
En sqlfiddle.com el resultado.

Debes tener presente lo que te comenta gnzsoloyo del cero (0) y NULL, en este caso dejo NULL para direfenciar de una NOTA con valor cero (0) y una NOTA que no existe (NULL), pero debes tratarlos correctamente.

Espero te sea útil.
  #4 (permalink)  
Antiguo 23/02/2013, 11:47
Avatar de sebandrescc2  
Fecha de Ingreso: diciembre-2012
Mensajes: 45
Antigüedad: 11 años, 4 meses
Puntos: 0
Respuesta: Como devolver registros sin valores
Cita:
Iniciado por gnzsoloyo Ver Mensaje
Para eso existe el LEFT JOIN:
Código MySQL: 
Ver original
  1. SELECT 
  2.     CONCAT(A.apellido, ' ', A.nombre) ALUMNO,
  3.     IFNULL(SUM(IF(id_parcial=1, nota, 0)), 0) nota1,
  4.     IFNULL(SUM(IF(id_parcial=2, nota, 0)), 0) nota2,
  5.     IFNULL(SUM(IF(id_parcial=3, nota, 0)), 0) nota3,
  6.     IFNULL(ROUND((sum(nota*(1-abs(sign(id_parcial-1)))) + sum(nota*(1-abs(sign(id_parcial-2)))) + sum(nota*(1-abs(sign(id_parcial-3)))))/COUNT(nota),1), 0) PROMEDIO
  7. FROM 
  8.     i_alumnos A LEFT JOIN t_notas N ON A.id_alumno = N.id_alumno
  9. WHERE 
  10.         N.id_curso=1
  11.     AND N.id_ramo=1
  12.     AND N.semestre=1
  13. GROUP BY A.apellido
  14. ORDER BY A.apellido asc

Tips:
1) AS está obsoleto desde hace años.
2) Si un valor no existe para un cálculo, se pone cero (0), no NULL. NULL destruye cualquier cálculo u operación realizada con ese valor.
3) Las comillas (") no se usan para las cadenas de texto a menos que el server esté específicamente configurado para ello. Se usan apóstrofos (').
4) Los valores numéricos no se ponen entre apóstrofos o comillas.
Hola gnzsoloyo,

Bueno agradezco los tips cosiderando que he tomado referencia de pdfs algo antiguos.

Con respecto a null, lo ocupé para diferenciar entre una nota inexistente y una nota 0.

Y con respecto a la consulta, me sigue obviando al alumno Sebastián que es el que no tiene calificaciones, pero pertenece al id_curso=1 que es el mismo resultado que logre con la tabla pivote.

Saludos y atento a tu respuesta.
__________________
Aquí voy a escribir mi firma
  #5 (permalink)  
Antiguo 23/02/2013, 12:03
Avatar de sebandrescc2  
Fecha de Ingreso: diciembre-2012
Mensajes: 45
Antigüedad: 11 años, 4 meses
Puntos: 0
Respuesta: Como devolver registros sin valores
Cita:
Iniciado por wchiquito Ver Mensaje
Hola sebandrescc2,

Trata con la siguiente sentencia:

Código MySQL: 
Ver original
  1. SELECT
  2.   dev.ALUMNO,
  3.   dev.NOTA1,
  4.   dev.NOTA2,
  5.   dev.NOTA3,
  6.   dev.PROMEDIO
  7. FROM (
  8.   SELECT
  9.     i_alumnos.id_alumno,
  10.     CONCAT(i_alumnos.apellido, ' ', i_alumnos.nombre) ALUMNO,
  11.     SUM(IF(t_notas.id_parcial = 1, t_notas.nota, NULL)) NOTA1,
  12.     SUM(IF(t_notas.id_parcial = 2, t_notas.nota, NULL)) NOTA2,
  13.     SUM(IF(t_notas.id_parcial = 3, t_notas.nota, NULL)) NOTA3,
  14.     ROUND((SUM(t_notas.nota * (1 - ABS(SIGN(t_notas.id_parcial - 1)))) +
  15.     SUM(t_notas.nota * (1 - ABS(SIGN(t_notas.id_parcial - 2)))) +
  16.     SUM(t_notas.nota * (1 - ABS(SIGN(t_notas.id_parcial - 3))))) / COUNT(t_notas.nota), 1) PROMEDIO
  17.   FROM i_alumnos
  18.     LEFT JOIN t_notas ON i_alumnos.id_alumno = t_notas.id_alumno
  19.       AND t_notas.id_curso = '1'
  20.       AND t_notas.id_ramo = '1'
  21.       AND t_notas.semestre = '1'
  22.   GROUP by i_alumnos.id_alumno, ALUMNO
  23.   ORDER by ALUMNO) dev;
En [URL="http://sqlfiddle.com/#!2/3d9dd/44"]sqlfiddle.com[/URL] el resultado.

Debes tener presente lo que te comenta [URL="http://www.forosdelweb.com/f86/como-devolver-registros-sin-valores-1038823/#post4376297"]gnzsoloyo[/URL] del cero (0) y NULL, en este caso dejo NULL para direfenciar de una NOTA con valor cero (0) y una NOTA que no existe (NULL), pero debes tratarlos correctamente.

Espero te sea útil.
Hola wchiquito,

Gracias por la respuesta.

Yo también usé left join para intentar resolver este problema, sin embargo me pasa lo mismo que con esta consulta, ya que aunque aparezca AND t_notas.id_curso = '1', aparecen los alumnos de todos los cursos (tres alumnos de id_curso=1 y dos de id_curso=2).

Deberían aparecer solo 3, y de esos 3 solo Contreras Castro Sebastián Andrés con todas sus notas null.

Ojala pudieras ayudarme con esto. saludos
__________________
Aquí voy a escribir mi firma
  #6 (permalink)  
Antiguo 23/02/2013, 12:18
Avatar de gnzsoloyo
Moderador criollo
  
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.324
Antigüedad: 16 años, 5 meses
Puntos: 2658
Respuesta: Como devolver registros sin valores
Cita:
me sigue obviando al alumno Sebastián que es el que no tiene calificaciones, pero pertenece al id_curso=1
No te lo está obviando. Es que tu estás poniendo tres condiciones obligatorias y ese alumno no cumple con dos de ellas, por tanto no pertenece al rango.
Básicamente te estás olvidando del funcionamiento del AND.

Para que devuelva lo que quieres se deben cumplir estas condiciones:
1) Pertenecer al curso Nº 1 y no tener notas.
2) Pertenecer al curso, tener notas, estar en el ramo Nº 1 y además ser notas del semestre Nº 1.
Es decir, hay dos juegos de condiciones diferentes, que tienen en común un sólo parámetro.
Ahora bien, como ese parámetro puede no darse en la tabla de notas cuando el alumno no tiene, entones debe evaluarse sólo para la primera tabla, mientrs que en el segundo caso hay que evaluar la segunda tabla.
El resto de los parámetros deben ser nulos, o bien tener 1 en ambos valores.

La consulta sería:
Código MySQL: 
Ver original
  1. SELECT 
  2.     CONCAT(A.apellido, ' ', A.nombre) ALUMNO,
  3.     IFNULL(SUM(IF(id_parcial=1, nota, 0)), 0) nota1,
  4.     IFNULL(SUM(IF(id_parcial=2, nota, 0)), 0) nota2,
  5.     IFNULL(SUM(IF(id_parcial=3, nota, 0)), 0) nota3,
  6.     IFNULL(ROUND((sum(nota*(1-ABS(SIGN(id_parcial-1)))) + sum(nota*(1-ABS(SIGN(id_parcial-2)))) + sum(nota*(1-ABS(SIGN(id_parcial-3)))))/COUNT(nota),1), 0) PROMEDIO
  7. FROM 
  8.     i_alumnos A LEFT JOIN t_notas N ON A.id_alumno = N.id_alumno
  9. WHERE 
  10.         (A.id_curso=1 
  11.     AND N.id_curso IS NULL)
  12.         OR
  13.         (N.id_curso = 1
  14.     AND N.id_ramo   = 1
  15.     AND N.semestre  = 1)
  16. GROUP BY A.apellido
  17. ORDER BY A.apellido asc

Trata de analizar claramente los grupos de condiciones que debe cumplir cada caso.

Prueba:
Código MySQL: 
Ver original
  1. mysql> SELECT
  2.     ->     CONCAT(A.apellido, ' ', A.nombre) ALUMNO,
  3.     ->     IFNULL(SUM(IF(id_parcial=1, nota, 0)), 0) nota1,
  4.     ->     IFNULL(SUM(IF(id_parcial=2, nota, 0)), 0) nota2,
  5.     ->     IFNULL(SUM(IF(id_parcial=3, nota, 0)), 0) nota3,
  6.     ->     IFNULL(ROUND((sum(nota*(1-ABS(SIGN(id_parcial-1)))) + sum(nota*(1-ABS(SIGN(id_parcial-2)))) + sum(nota*(1-ABS(SIGN(id_parcial-3)))))/COUNT(nota),1), 0) PROMEDIO
  7.     -> FROM
  8.     ->     i_alumnos A LEFT JOIN t_notas N ON A.id_alumno = N.id_alumno
  9.     -> WHERE
  10.     ->         (A.id_curso=1
  11.     ->     AND N.id_curso IS NULL)
  12.     ->         OR
  13.     ->         (N.id_curso = 1
  14.     ->     AND N.id_ramo   = 1
  15.     ->     AND N.semestre  = 1)
  16.     -> GROUP BY A.apellido
  17.     -> ORDER BY A.apellido asc;
  18. +-----------------------------------+-------+-------+-------+----------+
  19. | ALUMNO                            | nota1 | nota2 | nota3 | PROMEDIO |
  20. +-----------------------------------+-------+-------+-------+----------+
  21. | Castro Galarce Raquel             |   5.8 |   3.8 |   3.0 |      4.2 |
  22. | Contreras Castro Sebastißn AndrÚs |   0.0 |   0.0 |   0.0 |      0.0 |
  23. | Morgado Cifuentes Eduardo Alfredo |   7.0 |   7.0 |   6.0 |      6.7 |
  24. +-----------------------------------+-------+-------+-------+----------+
  25. 3 rows in set (0.00 sec)
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #7 (permalink)  
Antiguo 23/02/2013, 14:07
Avatar de sebandrescc2  
Fecha de Ingreso: diciembre-2012
Mensajes: 45
Antigüedad: 11 años, 4 meses
Puntos: 0
Respuesta: Como devolver registros sin valores
Saludos y gracias
__________________
Aquí voy a escribir mi firma
Última edición por sebandrescc2; 28/03/2014 a las 08:50

Etiquetas: devolver, registros, sql, tabla
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Tema Cerrado


« Tema Anterior | Próximo Tema »


La zona horaria es GMT -6. Ahora son las 09:45.