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

Suma desde 3 tablas con LEFT JOIN

Estas en el tema de Suma desde 3 tablas con LEFT JOIN en el foro de Mysql en Foros del Web. Hola que tal, Tengo una tabla Proyectos donde voy guardando obras, y a cada obra le corresponden 3 movimientos que los separé en 3 tablas: ...
  #1 (permalink)  
Antiguo 21/05/2017, 18:48
Avatar de sebandrescc2  
Fecha de Ingreso: diciembre-2012
Mensajes: 45
Antigüedad: 6 años, 11 meses
Puntos: 0
Suma desde 3 tablas con LEFT JOIN

Hola que tal,

Tengo una tabla Proyectos donde voy guardando obras, y a cada obra le corresponden 3 movimientos que los separé en 3 tablas: Pagos pg, Gastos g y Sueldos s.

Bueno, resulta que a la hora sumar pagos asociados a un proyecto me resultó bastante bien

Código MySQL:
Ver original
  1. COALESCE(ROUND(SUM( IF (pg.status_pago='PAGADO', pg.monto_pago, 0) + IF (exc_pago='SI', 0, (pg.monto_pago*0.19)) ),0), NULL, 0) as pago,
  2. p.nom_pro, c.nombre
  3.         FROM (proyectos p,clientes c)
  4.         LEFT JOIN pagos pg
  5.             ON p.id_pro = pg.id_pro
  6.            
  7.         WHERE p.id_cliente = c.id
  8.         GROUP BY p.id_pro, c.id
  9.         ORDER BY p.id_pro DESC



Pero, como necesito obtener en la misma fila los gastos y sueldos, me devuelve valores erróneos con esta consulta

Código MySQL:
Ver original
  1. COALESCE(ROUND(SUM( IF (pg.status_pago='PAGADO', pg.monto_pago, 0) + IF (exc_pago='SI', 0, (pg.monto_pago*0.19)) ),0), NULL, 0) as pago,
  2. COALESCE(ROUND(SUM( IF (g.status_gasto='PAGADO', g.monto_gasto, 0) + IF (exc_gasto='SI', 0, (g.monto_gasto*0.19)) ),0), NULL, 0) as gasto,
  3. COALESCE(ROUND(SUM( IF (s.status_sueldo='PAGADO', s.monto_sueldo, 0)),0),NULL, 0) as sueldo,
  4. p.nom_pro, c.nombre
  5.         FROM (proyectos p,clientes c)
  6.        
  7.         LEFT JOIN pagos pg
  8.             ON p.id_pro = pg.id_pro
  9.            
  10.         LEFT JOIN gastos g
  11.             ON p.id_pro = g.id_pro
  12.            
  13.         LEFT JOIN sueldos s
  14.             ON p.id_pro = s.id_pro
  15.            
  16.         WHERE p.id_cliente = c.id
  17.         GROUP BY p.id_pro, c.id
  18.         ORDER BY p.id_pro DESC



Incluso marque en rojo como uno de ellos cambia, no se por qué?
Que hago mal?. Como dato, todas las tablas están relacionadas con id_pro

Bueno, espero que me puedan ayudar... Gracias !!
__________________
Aquí voy a escribir mi firma
  #2 (permalink)  
Antiguo 22/05/2017, 09:20
Colaborador
 
Fecha de Ingreso: enero-2007
Ubicación: México
Mensajes: 2.097
Antigüedad: 12 años, 10 meses
Puntos: 447
Respuesta: Suma desde 3 tablas con LEFT JOIN

Hola sebandrescc2:

No nos pones datos de ejemplo, pero creo saber por donde va el problema. De entrada te comento que no es recomendable que mezcles JOIN'S implícitos con JOIN's explícitos... es decir, por un lado tienes en el FROM esto:

Código:
...
  FROM (proyectos p,clientes c)
...
  WHERE 
    p.id_cliente = c.id
...
peor al mismo tiempo estás realizando varios LEFT JOIN... la primer forma YA NO ES RECOMENDABLE, en lugar de esto puedes hacer simplemente un INNER JOIN así:

Código:
...
  FROM proyectos p
  INNER JOIN clientes c ON p.id_cliente = c.id
  LEFT JOIN ...
...
Ahora bien, pasando al tema de los datos erroneos. puedo suponer que tienes una relación 1 a muchos entre cada una de tus tablas, es decir, y proyecto puede tener muchos pagos, muchos gastos y muchos salarios. Cuando haces un JOIN entre dos tablas así NO HAY PROBLEMA, pero cuando incluyes una tercer tabla entonces la relación pasa a ser de MUCHOS A MUCHOS, por lo tanto los cálculos ya no son iguales. Trataré de poner un ejemplo práctico. imagina que tienes esto:

Código MySQL:
Ver original
  1. mysql> SELECT * FROM proyectos;
  2. +-------------+---------------+
  3. | id_proyecto | descripcion   |
  4. +-------------+---------------+
  5. |           1 | PROYECTO UNO  |
  6. |           2 | PROYECTO DOS  |
  7. |           3 | PROYECTO TRES |
  8. +-------------+---------------+
  9. 3 rows in set (0.13 sec)
  10.  
  11. mysql> SELECT * FROM pagos;
  12. +---------+-------------+---------------+
  13. | id_pago | id_proyecto | cantidad_pago |
  14. +---------+-------------+---------------+
  15. |       1 |           1 |            10 |
  16. |       2 |           1 |            20 |
  17. |       3 |           1 |            30 |
  18. |       4 |           2 |            40 |
  19. |       5 |           2 |            40 |
  20. |       6 |           3 |            50 |
  21. +---------+-------------+---------------+
  22. 6 rows in set (0.01 sec)
  23.  
  24. mysql> SELECT * FROM gastos;
  25. +----------+-------------+----------------+
  26. | id_gasto | id_proyecto | cantidad_gasto |
  27. +----------+-------------+----------------+
  28. |        1 |           1 |             15 |
  29. |        2 |           1 |             10 |
  30. |        3 |           2 |             30 |
  31. +----------+-------------+----------------+
  32. 3 rows in set (0.00 sec)

ahora bien, cuando unes PROYECTOS con PAGOS sin hacer sumas ni agrupaciones observa el resultado:

Código MySQL:
Ver original
  1. mysql> SELECT *
  2.     -> FROM proyectos
  3.     -> LEFT JOIN pagos ON proyectos.id_proyecto = pagos.id_proyecto;
  4. +-------------+---------------+---------+-------------+---------------+
  5. | id_proyecto | descripcion   | id_pago | id_proyecto | cantidad_pago |
  6. +-------------+---------------+---------+-------------+---------------+
  7. |           1 | PROYECTO UNO  |       1 |           1 |            10 |
  8. |           1 | PROYECTO UNO  |       2 |           1 |            20 |
  9. |           1 | PROYECTO UNO  |       3 |           1 |            30 |
  10. |           2 | PROYECTO DOS  |       4 |           2 |            40 |
  11. |           2 | PROYECTO DOS  |       5 |           2 |            40 |
  12. |           3 | PROYECTO TRES |       6 |           3 |            50 |
  13. +-------------+---------------+---------+-------------+---------------+
  14. 6 rows in set (0.00 sec)

Observa que la descripción para cada proyecto SE REPITE EN CADA REGISTRO DE PAGO... ahora observa qué pasa cuando incluyes la tabla de GASTOS:

Código MySQL:
Ver original
  1. mysql> SELECT *
  2.     -> FROM proyectos
  3.     -> LEFT JOIN pagos ON proyectos.id_proyecto = pagos.id_proyecto
  4.     -> LEFT JOIN gastos ON proyectos.id_proyecto = gastos.id_proyecto;
  5. +-------------+---------------+---------+-------------+---------------+----------+-------------+----------------+
  6. | id_proyecto | descripcion   | id_pago | id_proyecto | cantidad_pago | id_gasto | id_proyecto | cantidad_gasto |
  7. +-------------+---------------+---------+-------------+---------------+----------+-------------+----------------+
  8. |           1 | PROYECTO UNO  |       1 |           1 |            10 |        1 |           1 |             15 |
  9. |           1 | PROYECTO UNO  |       2 |           1 |            20 |        1 |           1 |             15 |
  10. |           1 | PROYECTO UNO  |       3 |           1 |            30 |        1 |           1 |             15 |
  11. |           1 | PROYECTO UNO  |       1 |           1 |            10 |        2 |           1 |             10 |
  12. |           1 | PROYECTO UNO  |       2 |           1 |            20 |        2 |           1 |             10 |
  13. |           1 | PROYECTO UNO  |       3 |           1 |            30 |        2 |           1 |             10 |
  14. |           2 | PROYECTO DOS  |       4 |           2 |            40 |        3 |           2 |             30 |
  15. |           2 | PROYECTO DOS  |       5 |           2 |            40 |        3 |           2 |             30 |
  16. |           3 | PROYECTO TRES |       6 |           3 |            50 |     NULL |        NULL |           NULL |
  17. +-------------+---------------+---------+-------------+---------------+----------+-------------+----------------+
  18. 9 rows in set (0.00 sec)

observa ahora los ID_PAGOS, estos se duplicaron también tantas veces como gasto existen!!! y entre más tablas sigas uniendo más registros se irán duplicando... ¿QUÉ TIENES QUE HACER, pues cambiar tu relación 1 a muchos a una relación 1 a 1, en otras palabras primero CONSOLIDAR los PAGOS, los GASTOS y los SUELDOS por SEPARADO y después unirlos:

Primero obtienes el total de pagos POR PROYECTO:

Código MySQL:
Ver original
  1. mysql> SELECT id_proyecto, SUM(cantidad_pago) total_pagos
  2.     -> FROM pagos
  3.     -> GROUP BY id_proyecto;
  4. +-------------+-------------+
  5. | id_proyecto | total_pagos |
  6. +-------------+-------------+
  7. |           1 |          60 |
  8. |           2 |          80 |
  9. |           3 |          50 |
  10. +-------------+-------------+
  11. 3 rows in set (0.00 sec)

Y haces lo mismo con los gastos:

Código Python:
Ver original
  1. mysql> SELECT id_proyecto, SUM(cantidad_gasto) total_gasto
  2.     -> FROM gastos
  3.     -> GROUP BY id_proyecto;
  4. +-------------+-------------+
  5. | id_proyecto | total_gasto |
  6. +-------------+-------------+
  7. |           1 |          25 |
  8. |           2 |          30 |
  9. +-------------+-------------+
  10. 2 rows in set (0.00 sec)

ahora si, OBSERVA QUE SOLO HAY UN CONCEPTO DE PAGO y UN GASTO por cada proyecto, por lo tanto tu relación es 1 a 1, entonces puedes hacer el LEFT JOIN utilizando estas consultas:

Código MySQL:
Ver original
  1. mysql> SELECT proyectos.id_proyecto, proyectos.descripcion, pagos.total_pagos, gastos.total_gastos
  2.     -> FROM proyectos
  3.     -> LEFT JOIN (SELECT id_proyecto, SUM(cantidad_pago) total_pagos
  4.     ->            FROM pagos
  5.     ->            GROUP BY id_proyecto) pagos
  6.     ->      ON proyectos.id_proyecto = pagos.id_proyecto
  7.     -> LEFT JOIN (SELECT id_proyecto, SUM(cantidad_gasto) total_gastos
  8.     ->            FROM gastos
  9.     ->            GROUP BY id_proyecto) gastos
  10.     ->      ON proyectos.id_proyecto = gastos.id_proyecto;
  11. +-------------+---------------+-------------+--------------+
  12. | id_proyecto | descripcion   | total_pagos | total_gastos |
  13. +-------------+---------------+-------------+--------------+
  14. |           1 | PROYECTO UNO  |          60 |           25 |
  15. |           2 | PROYECTO DOS  |          80 |           30 |
  16. |           3 | PROYECTO TRES |          50 |         NULL |
  17. +-------------+---------------+-------------+--------------+
  18. 3 rows in set (0.02 sec)


¿Se entiende? dale un vistazo y nos comentas.

Saludos
Leo.
  #3 (permalink)  
Antiguo 23/05/2017, 00:12
Avatar de sebandrescc2  
Fecha de Ingreso: diciembre-2012
Mensajes: 45
Antigüedad: 6 años, 11 meses
Puntos: 0
Respuesta: Suma desde 3 tablas con LEFT JOIN

Gracias Leo muy didáctica la explicación.
Hay varias cosas que no sabía que se podían hacer, como los select dentro del join. Funciona perfecto y aquí el código para que le sirva a alguien mas

Código MySQL:
Ver original
  1. SELECT proyectos.id_pro, proyectos.nom_pro, clientes.nombre, pagos.total_pagos, gastos.total_gastos , sueldos.total_sueldos
  2.     FROM proyectos
  3.    
  4.         INNER JOIN clientes
  5.                 ON proyectos.id_cliente = clientes.id
  6.        
  7.         LEFT JOIN (SELECT id_pro, COALESCE(ROUND(SUM( IF (status_pago='PAGADO', monto_pago, 0) + IF (exc_pago='SI', 0, (monto_pago*0.19)) ),0), NULL, 0) total_pagos
  8.         FROM pagos
  9.         GROUP BY id_pro) pagos
  10.                 ON proyectos.id_pro = pagos.id_pro
  11.    
  12.         LEFT JOIN (SELECT id_pro, COALESCE(ROUND(SUM( IF (status_gasto='PAGADO', monto_gasto, 0) + IF (exc_gasto='SI', 0, (monto_gasto*0.19)) ),0), NULL, 0) total_gastos
  13.         FROM gastos
  14.         GROUP BY id_pro) gastos
  15.                 ON proyectos.id_pro = gastos.id_pro
  16.    
  17.         LEFT JOIN (SELECT id_pro, COALESCE(ROUND(SUM( IF (status_sueldo='PAGADO', monto_sueldo, 0)),0),NULL, 0) total_sueldos
  18.         FROM sueldos
  19.         GROUP BY id_pro) sueldos
  20.                 ON proyectos.id_pro = sueldos.id_pro
  21.            
  22.         ORDER BY proyectos.id_pro DESC
__________________
Aquí voy a escribir mi firma

Etiquetas: join, left, select, suma, tabla, tablas, valor
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.
Respuesta




La zona horaria es GMT -6. Ahora son las 03:25.