Left join con problemas

[clinisbut]

Hola a todos.

Tengo un problema con una consulta, la cual no me da los resultados esperados.
Mis tablas son las siguientes (simplificadas)
--pedidos--
id


--conceptos--
id
id_pedido


--facturas--
id
id_pedido
id_concepto


La cuestion es que para un pedido, quiero contar cuantos conceptos tiene, pero tambien quiero contar cuantas facturas se han rellenado (cada factura es la factura de uno de estos conceptos, la suma de todos los conceptos es el pedido).

Lo intento de la siguiente manera:

Código:

SELECT COUNT(facturas.id) AS num, COUNT(conceptos.id) as total
FROM pedidos LEFT JOIN conceptos ON ( conceptos.id_pedido=pedidos.id)
LEFT JOIN facturas ON ( facturas.id_pedido=pedidos.id )
GROUP BY pedidos.id

(La escribo de memoria, igual hay algun fallo)

Lo que obtengo son unos resultados totalmente desproporcionados: count (ambos) me devuelve en ocasiones valores por encima de 500 cuando lo tipico es <20.

Además de una lentitud insoportable (5 minutos).

El tamaño de estas tablas no es exagerado, no llega a 3000 resultados por tabla (la mas grande quizá sea la tabla conceptos).
Sospecho que se esta cruzando todo multiples veces por la lentitud y los resultados de count.

Que hago mal?
Muchas gracias.

[gnzsoloyo]

Esto:

Código sql:

SELECT 
    COUNT(facturas.id) num, 
    COUNT(conceptos.id) total
FROM pedidos p LEFT  JOIN conceptos c ON p.id = c.id_pedido
   LEFT JOIN facturas f ON p.id = f.id_pedido
GROUP BY pedidos.id
 

podría estar duplicando ciertos registros, ya que LEFT JOIN opera sobre la tabla izquierda (la primera) y devuelve todas las combinaciones con la derecha, incluyendo los resultados NULL, pero en ese caso el campo de la primera tabla se repite, con lo que pasa a ser válido y facturas.id puede estar sumándose de nuevo.

Yo lo probaría así:

Código sql:

SELECT 
    COUNT(facturas.id) num, 
    COUNT(conceptos.id) total
FROM pedidos p INNER JOIN conceptos c ON p.id = c.id_pedido
   INNER JOIN facturas f ON p.id = f.id_pedido
GROUP BY pedidos.id
 

INNER JOIN te devolverá sólo aquellos en los que el campo en ON coincida.

En cuanto a la cantidad de registro, no te extrañe que tarde, porque en ese contexto (3.000 registros/tabla), LEFT JOIN podría estar procesando 18.000.000.000 registros para realizar la cuenta. Además, los GROUP BY llevan tiempo.

Usa EXPLAIN para ver cómo está cruzando los datos y si está usando índices.

[clinisbut]

Iniciado por gnzsoloyo

Esto:
Yo lo probaría así:

Código sql:

SELECT 
    COUNT(facturas.id) num, 
    COUNT(conceptos.id) total
FROM pedidos p INNER JOIN conceptos c ON p.id = c.id_pedido
   INNER JOIN facturas f ON p.id = f.id_pedido
GROUP BY pedidos.id
 

INNER JOIN te devolverá sólo aquellos en los que el campo en ON coincida.

Pero entonces no me devolverá los pedidos que no tengan ninguna factura, verdad? Necesito obtener todos los pedidos, pero eso he estado usando pedidos.

Por otro lado, al decir tu

ya que LEFT JOIN opera sobre la tabla izquierda (la primera) y devuelve todas las combinaciones con la derecha

Te refieres a que el segundo LEFT JOIN opera sobre el resultado del primer LEFT JOIN?

[gnzsoloyo]

Bien, volviendo al principio, tu necesitas entonces tres datos, no dos como parecía:
- Cantidad de pedidos.
- Cantidad de conceptos en cada pedido.
- Cantidad de facturas confeccionadas por pedidos.

Según el modelo que pusiste arriba, entonces, para que un concepto exista, debe forzosamente existir un pedido. Como lo que tu quieres es la cantidad de ambos, en realidad la información está en la misma tabla (conceptos), por lo que estos dos primeros datos surgirían de hacer:

Código sql:

SELECT 
    COUNT(DISTINCT c.id_pedido) CantPedidos, 
    COUNT(c.id) totalConceptos
FROM conceptos c 
GROUP BY id_pedido
 

Entonces, para ver cuántas facturas hay, deberíamos relacionar esta tabla con la de facturas:

Código sql:

SELECT 
    COUNT(DISTINCT c.id_pedido) CantPedidos, 
    COUNT(c.id) totalConceptos, 
    COUNT(f.id) totalFacturas
FROM conceptos c LEFT JOIN facturas f ON p.id_pedido = f.id_pedido
GROUP BY c.id_pedido
 

Esta consulta se basa en la suposición de que hay una factura por concepto, y como cada concepto pertenece sólo a un pedido, hay una relación 1:1 entre los dos campos por cada ID de factura.

Probemos...

[clinisbut]

Bien, volviendo al principio, tu necesitas entonces tres datos, no dos como parecía:

Si cierto... olvidé indicar la columna pedidos.id en la primera consulta. Fallo mío.

Según el modelo que pusiste arriba, entonces, para que un concepto exista, debe forzosamente existir un pedido. Como lo que tu quieres es la cantidad de ambos, en realidad la información está en la misma tabla (conceptos), por lo que estos dos primeros datos surgirían de hacer:

mmm no caí en que el pedido (el id) ya lo tengo en conceptos... aunque en realidad no necesito contar el numero de pedidos, sino que para cada pedido (pedidos.id) necesito saber el total de conceptos existentes (COUNT(conceptos.id) ) y el numero de facturas que se han rellenado ( COUNT(facturas.id) ).

Tiene buena pinta, en cuanto pueda la pruebo y comento.

Muchas gracias!

[gnzsoloyo]

para cada pedido (pedidos.id) necesito saber el total de conceptos existentes (COUNT(conceptos.id) )

en ese caso, debes dejar el id_concepto y agrupar por él, para que se vea a qué pedidos corresponden las cantidades.

[clinisbut]

Te refieres a:

Código:

SELECT 
    conceptos.id_pedido, 
    COUNT(c.id) totalConceptos, 
    COUNT(f.id) totalFacturas
FROM conceptos c LEFT JOIN facturas f ON p.id_pedido = f.id_pedido
GROUP BY conceptos.id_pedido

verdad??

[gnzsoloyo]

Exacto.
De esa forma el conjunto devuelto se hace mas claro.

[clinisbut]

Bueno, finalmente lo he conseguido. Pero con alguna modificacion ya que se seguian cruzando registros:

Código:

SELECT
   c.id_pedido,
   COUNT(c.id) totalConceptos,
    COUNT(f.id) totalFacturas
FROM concepto_pedidos AS c LEFT JOIN facturas AS f ON (c.id = f.id_concepto_pedidos)
GROUP BY c.id_pedido ORDER BY totalConceptos DESC

He tenido que relacionarlo por el id_concepto. Ahora si que sale perfecto.
Muchas gracias!