INNER JOIN con tres tablas y función SUM

[luis_enr27]

Hola Buen día tengo un serio problema con sum y los inner join en SQL... Espero puedan ayudarme
Bueno quiero hacer una consulta a 3 tablas y poder obtener la suma del campo ventas_TM y suma de ventas_TM de la tabla de ventas, a su vez la suma de gasto de la tabla gastos y tambien la suma de depositos de la tabla depositos; Todo esto que cumpla la condicion que pido en donde el rango de fechas seleccionado, de la farmacia seleccionada y del turno donde sea diferente a completo.

TABLAS:
ventas (id_venta,fecha,farmacia, usuario_TM, ventas_TM,usuario_TB,ventas_TV)
gastos (id_gasto,fecha,farmacia,usuario,turno,gasto)
depositos (id_deposto, fecha,farmacia,usuario,turno,deposito)

CONSULTA:
SELECT
ventas.fecha,
usuario_TM,
SUM( DISTINCT ventas_TM) as TM,
usuario_TV,
SUM(DISTINCT ventas_TV) as TV,
SUM(DISTINCT (ventas_TM+ventas_TV)) as TOTAL_VENTAS,
CASE WHEN COUNT(gasto)>1 THEN ifnull((SUM(gasto)/count(DISTINCT
id_deposito)), (SUM(gasto)/1))
ELSE ifnull(SUM(DISTINCT gasto),0)
END,
CASE WHEN COUNT(deposito)>1 THEN ifnull((SUM(deposito)/count(distinct id_gasto)), (SUM(deposito)/1))
ELSE ifnull(SUM(DISTINCT deposito),0)
END
FROM ventas INNER JOIN gastos INNER JOIN depositos
ON ventas.fecha=gastos.fecha AND gastos.fecha=depositos.fecha
WHERE ventas.farmacia='nombre_farmacia' AND ventas.fecha between '2013-06-01' AND '2013-06-15' AND
gastos.turno!='COMPLETO' AND depositos.turno!='COMPLETO'
GROUP BY ventas.fecha
ORDER BY ventas.fecha ASC;

El problema es que utilizo un while para la obtencion de informacion y llenado de un reporte pero me devuelve informacion erronea cuando hago una consulta, ya que me multiplica la informacion(sumas) por el numero de registros de la tabla de union es por ello qe hago case, pero aun asi cuando no hay un registro en la otra tabla que cumpla la condicion no regresa ningun dato.
relaciono las tablas por fecha y por farmacia ya que con el id no puedo, ya que hay ocacionesque alguna de las tres tablas puede tener de 0 a muchos registros. espero entiendan

[leonardo_josue]

Hola luis_enr27:

Este problema es bastante típico, y por lo general se ocasiona porque no se tiene claro el función de los JOIN's... vayamos por partes...

El problema es que utilizo un while para la obtencion de informacion y llenado de un reporte pero me devuelve informacion erronea cuando hago una consulta, ya que me multiplica la informacion(sumas) por el numero de registros de la tabla de union es por ello qe hago case

En este punto el problema puede estar en la cardinalidad de tus tablas, es decir cómo están relacionadas tus tablas... (puede ser 1 a 1, 1 a n o 0 a n)... el detalle al decir que te está "multiplicando" la información me hace suponer que tienes una relación 1 a n, pero la suma no la haces de manera consolidada... Veamos este ejemplo. Supongamos que tienes estos datos:

Código MySQL:

mysql> SELECT * FROM ventas;
+---------+-------+
| idventa | monto |
+---------+-------+
|       1 |   100 |
|       2 |   200 |
|       3 |   300 |
+---------+-------+
3 rows in set (0.00 sec)
 
mysql> SELECT * FROM depositos;
+------------+---------+-------+
| iddeposito | idventa | monto |
+------------+---------+-------+
|          1 |       1 |    25 |
|          2 |       1 |    20 |
|          3 |       2 |   100 |
|          4 |       1 |    20 |
|          5 |       2 |    50 |
+------------+---------+-------+
5 rows in set (0.00 sec)
 

es decir, tres ventas con un monto y varios depósitos para cada venta a manera de abonos a la cuenta... (no uso los mismos campos que tú, pero la idea es la misma)... supongamos que quieres saber cuánto se ha depositado a cada venta... La idea es SUMAR todos los depósitos e igualarlos para cada venta, pero observa que tienes una relación 0 a n, es decir, puede haber depósitos o no para cada venta... si haces un JOIN entre las tablas, olvidándote por ahora de las sumas observa qué pasa:

Código MySQL:

mysql> SELECT * FROM ventas
    -> INNER JOIN depositos ON ventas.idventa = depositos.idventa;
+---------+-------+------------+---------+-------+
| idventa | monto | iddeposito | idventa | monto |
+---------+-------+------------+---------+-------+
|       1 |   100 |          1 |       1 |    25 |
|       1 |   100 |          2 |       1 |    20 |
|       2 |   200 |          3 |       2 |   100 |
|       1 |   100 |          4 |       1 |    20 |
|       2 |   200 |          5 |       2 |    50 |
+---------+-------+------------+---------+-------+
5 rows in set (0.00 sec)
 

Observa en primer lugar, que por cada registro en tu tabla depósitos se está repitiendo el monto de la venta... por lo tanto, si agrupas por venta y quieres obtener el monto de la venta, pues daría la impresión de que se multiplicó... además, la venta 3 no tiene ningún depósito, por lo tanto al utilizar INNER JOIN no se presenta...

Código MySQL:

mysql> SELECT ventas.idventa, 
    -> SUM(ventas.monto) total_venta, SUM(depositos.monto) total_depositos
    -> FROM ventas
    -> INNER JOIN depositos ON ventas.idventa = depositos.idventa
    -> GROUP BY ventas.idventa;
+---------+-------------+-----------------+
| idventa | total_venta | total_depositos |
+---------+-------------+-----------------+
|       1 |         300 |              65 |
|       2 |         400 |             150 |
+---------+-------------+-----------------+
2 rows in set (0.00 sec)
 

Como dije al inicio, el problema es la cardinalidad de tus tablas... debes convertir tu relación 1 a n en una relación 1 a 1... es decir, sumar primeramente todos los depósitos antes de hacer el JOIN... además, en lugar de utilizar un INNER JOIN, debes hacer uso de LEFT JOIN o RIGTH JOIN, para que las relaciones no sean obligadas. sería más o menos así:

Código MySQL:

mysql> SELECT * FROM ventas LEFT JOIN
    -> (  SELECT idventa, SUM(monto) total_depositos
    ->    FROM depositos GROUP BY idventa
    -> ) depositos ON ventas.idventa = depositos.idventa;
+---------+-------+---------+-----------------+
| idventa | monto | idventa | total_depositos |
+---------+-------+---------+-----------------+
|       1 |   100 |       1 |              65 |
|       2 |   200 |       2 |             150 |
|       3 |   300 |    NULL |            NULL |
+---------+-------+---------+-----------------+
3 rows in set (0.00 sec)
 

de esta manera las sumas son correctas y además presenta todos los registros, no solo aquellos que tengan registros en la otra tabla...

observa que en el JOIN se hace una subconsulta, agrupando previamente. de esta manera se evita la "multiplicación" de las cantidades.

Dale un vistazo y trata de implementar este tipo de consultas a lo que quieres hacer.

Saludos
Leo.