Suma desde 3 tablas con LEFT JOIN

[sebandrescc2]

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:

SELECT 
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, 
p.nom_pro, c.nombre
        FROM (proyectos p,clientes c)
        LEFT JOIN pagos pg
            ON p.id_pro = pg.id_pro
            
        WHERE p.id_cliente = c.id
        GROUP BY p.id_pro, c.id
        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:

SELECT 
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,
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,
COALESCE(ROUND(SUM( IF (s.status_sueldo='PAGADO', s.monto_sueldo, 0)),0),NULL, 0) as sueldo,
p.nom_pro, c.nombre
        FROM (proyectos p,clientes c)
        
        LEFT JOIN pagos pg
            ON p.id_pro = pg.id_pro
            
        LEFT JOIN gastos g 
            ON p.id_pro = g.id_pro
            
        LEFT JOIN sueldos s 
            ON p.id_pro = s.id_pro
            
        WHERE p.id_cliente = c.id
        GROUP BY p.id_pro, c.id
        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 !!

[leonardo_josue]

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:

mysql> SELECT * FROM proyectos;
+-------------+---------------+
| id_proyecto | descripcion   |
+-------------+---------------+
|           1 | PROYECTO UNO  |
|           2 | PROYECTO DOS  |
|           3 | PROYECTO TRES |
+-------------+---------------+
3 rows in set (0.13 sec)
 
mysql> SELECT * FROM pagos;
+---------+-------------+---------------+
| id_pago | id_proyecto | cantidad_pago |
+---------+-------------+---------------+
|       1 |           1 |            10 |
|       2 |           1 |            20 |
|       3 |           1 |            30 |
|       4 |           2 |            40 |
|       5 |           2 |            40 |
|       6 |           3 |            50 |
+---------+-------------+---------------+
6 rows in set (0.01 sec)
 
mysql> SELECT * FROM gastos;
+----------+-------------+----------------+
| id_gasto | id_proyecto | cantidad_gasto |
+----------+-------------+----------------+
|        1 |           1 |             15 |
|        2 |           1 |             10 |
|        3 |           2 |             30 |
+----------+-------------+----------------+
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:

mysql> SELECT *
    -> FROM proyectos
    -> LEFT JOIN pagos ON proyectos.id_proyecto = pagos.id_proyecto;
+-------------+---------------+---------+-------------+---------------+
| id_proyecto | descripcion   | id_pago | id_proyecto | cantidad_pago |
+-------------+---------------+---------+-------------+---------------+
|           1 | PROYECTO UNO  |       1 |           1 |            10 |
|           1 | PROYECTO UNO  |       2 |           1 |            20 |
|           1 | PROYECTO UNO  |       3 |           1 |            30 |
|           2 | PROYECTO DOS  |       4 |           2 |            40 |
|           2 | PROYECTO DOS  |       5 |           2 |            40 |
|           3 | PROYECTO TRES |       6 |           3 |            50 |
+-------------+---------------+---------+-------------+---------------+
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:

mysql> SELECT *
    -> FROM proyectos
    -> LEFT JOIN pagos ON proyectos.id_proyecto = pagos.id_proyecto
    -> LEFT JOIN gastos ON proyectos.id_proyecto = gastos.id_proyecto;
+-------------+---------------+---------+-------------+---------------+----------+-------------+----------------+
| id_proyecto | descripcion   | id_pago | id_proyecto | cantidad_pago | id_gasto | id_proyecto | cantidad_gasto |
+-------------+---------------+---------+-------------+---------------+----------+-------------+----------------+
|           1 | PROYECTO UNO  |       1 |           1 |            10 |        1 |           1 |             15 |
|           1 | PROYECTO UNO  |       2 |           1 |            20 |        1 |           1 |             15 |
|           1 | PROYECTO UNO  |       3 |           1 |            30 |        1 |           1 |             15 |
|           1 | PROYECTO UNO  |       1 |           1 |            10 |        2 |           1 |             10 |
|           1 | PROYECTO UNO  |       2 |           1 |            20 |        2 |           1 |             10 |
|           1 | PROYECTO UNO  |       3 |           1 |            30 |        2 |           1 |             10 |
|           2 | PROYECTO DOS  |       4 |           2 |            40 |        3 |           2 |             30 |
|           2 | PROYECTO DOS  |       5 |           2 |            40 |        3 |           2 |             30 |
|           3 | PROYECTO TRES |       6 |           3 |            50 |     NULL |        NULL |           NULL |
+-------------+---------------+---------+-------------+---------------+----------+-------------+----------------+
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:

mysql> SELECT id_proyecto, SUM(cantidad_pago) total_pagos
    -> FROM pagos
    -> GROUP BY id_proyecto;
+-------------+-------------+
| id_proyecto | total_pagos |
+-------------+-------------+
|           1 |          60 |
|           2 |          80 |
|           3 |          50 |
+-------------+-------------+
3 rows in set (0.00 sec)
 

Y haces lo mismo con los gastos:

Código Python:

mysql> SELECT id_proyecto, SUM(cantidad_gasto) total_gasto
    -> FROM gastos
    -> GROUP BY id_proyecto;
+-------------+-------------+
| id_proyecto | total_gasto |
+-------------+-------------+
|           1 |          25 |
|           2 |          30 |
+-------------+-------------+
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:

mysql> SELECT proyectos.id_proyecto, proyectos.descripcion, pagos.total_pagos, gastos.total_gastos
    -> FROM proyectos
    -> LEFT JOIN (SELECT id_proyecto, SUM(cantidad_pago) total_pagos
    ->            FROM pagos
    ->            GROUP BY id_proyecto) pagos
    ->      ON proyectos.id_proyecto = pagos.id_proyecto
    -> LEFT JOIN (SELECT id_proyecto, SUM(cantidad_gasto) total_gastos
    ->            FROM gastos
    ->            GROUP BY id_proyecto) gastos
    ->      ON proyectos.id_proyecto = gastos.id_proyecto;
+-------------+---------------+-------------+--------------+
| id_proyecto | descripcion   | total_pagos | total_gastos |
+-------------+---------------+-------------+--------------+
|           1 | PROYECTO UNO  |          60 |           25 |
|           2 | PROYECTO DOS  |          80 |           30 |
|           3 | PROYECTO TRES |          50 |         NULL |
+-------------+---------------+-------------+--------------+
3 rows in set (0.02 sec)
 

¿Se entiende? dale un vistazo y nos comentas.

Saludos
Leo.

[sebandrescc2]

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:

SELECT proyectos.id_pro, proyectos.nom_pro, clientes.nombre, pagos.total_pagos, gastos.total_gastos , sueldos.total_sueldos
    FROM proyectos
    
        INNER JOIN clientes
                ON proyectos.id_cliente = clientes.id 
        
        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
        FROM pagos
        GROUP BY id_pro) pagos
                ON proyectos.id_pro = pagos.id_pro
    
        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
        FROM gastos
        GROUP BY id_pro) gastos
                ON proyectos.id_pro = gastos.id_pro
    
        LEFT JOIN (SELECT id_pro, COALESCE(ROUND(SUM( IF (status_sueldo='PAGADO', monto_sueldo, 0)),0),NULL, 0) total_sueldos
        FROM sueldos
        GROUP BY id_pro) sueldos
                ON proyectos.id_pro = sueldos.id_pro
            
        ORDER BY proyectos.id_pro DESC