conteo junto con joins devuelve mas resultados de los esperados

[mayid]

El caso es que quiero contar valores de una tabla, y en lo posible hacer join a otras tablas para tomar en cuenta un dato. Pero al hacer los joins se incrementa el numero de los resultados. Y en principio no pude resolverlo con un GROUP BY.

Código MySQL:

SELECT ( 
     SELECT count(*) FROM Payments
      LEFT JOIN Commissions_Payments AS CP USING (pay_id)
      LEFT JOIN Commissions AS comm USING (comm_id)
   WHERE user_id_approver IS NOT NULL AND user_id_payer IS NULL
     AND comm_payed = 0 
    ) AS approved
 

Quiero aprender SQL mas a fondo. Porque si bien a veces escribo consultas "complejas" todavía no se muy bien como interpreta secuencialmente las querys. Si tienen algun link como para dar un paso mas, les agradecería.

[timoteo666]

Por que no intentas con INNER JOIN en lugar de LEFT JOIN también te recomiendo que utilices alias 'AS' para las tablas.
http://www.maestrosdelweb.com/editorial/tutsql1/

[mayid]

Si, es cierto que INNER es lo correcto. Y para las tablas si uso alias, como podes ver. En lo que publiqué falta el alias para Payments, ok. Pero igual, si lo tuviera e hiciera esto: count(p.*) no lograría un resultado (sino un error, creo). Por qué?

[gnzsoloyo]

Proque usando el asterisco cuentas los registros devueltos, tengan o no relación. Lo que debes hacer es usar LEFT JOIN y contar el campo de relación. Como en SQL el NULL no es un dato, no se contará.

[mayid]

Consiejo: Jamás uses la coma como JOIN, es siempre preferible usar INNER/LEFT/RIGHT JOIN, es mucho más eficiente por varias razones.

Gracias por el consejo, que no tenía tan presente la cuestion, y a veces si usaba comas.

Respecto a la consulta, si descomento las lineas siguientes obtengo 12 como resultado. Sin embargo, ejecutandola así como se ve, obtengo 4 (no 4 rows, sino el numero 4 por el count).

Código MySQL:

SELECT (
         SELECT COUNT(*) FROM Payments p
        # LEFT JOIN Commissions_Payments AS CP USING (pay_id)
         # LEFT JOIN Commissions AS comm USING (comm_id)
       WHERE user_id_approver IS NOT NULL AND user_id_payer IS NULL
         #AND comm_payed = 0         
        ) AS approved
 

Si intento algo así COUNT(p.*) me da error. Y si pongo un group by tambien. O sea, como usar joins para tomar una condicion de otra tabla? No estoy pudiendo hacerlo.

[mayid]

Funcionó usar DISTINCT sobre el primary key (en vez de usar el asterisco):

Código MySQL:

SELECT (
         SELECT COUNT( DISTINCT p.pay_id ) FROM Payments p
         LEFT JOIN Commissions_Payments AS CP USING (pay_id)
          LEFT JOIN Commissions AS comm USING (comm_id)
       WHERE user_id_approver IS NOT NULL AND user_id_payer IS NULL
         AND comm_payed = 0         
        ) AS approved
 

Creo que lo que estoy haciendo es similar a un group by.

Si alguien tiene un link a tutoriales SQL que hablen de estos temas, buenisimo.

[gnzsoloyo]

Es un poco más efectivo si usas la PK de la segunda tabla, pero para el caso no es problema:

Código MySQL:

SELECT 
    COUNT( DISTINCT CP.pay_id ) Cant_pay_id
FROM Payments p 
    LEFT JOIN Commissions_Payments CP USING (pay_id)
    LEFT JOIN Commissions comm USING (comm_id)
WHERE 
    user_id_approver IS NOT NULL AND 
    user_id_payer IS NULL
    AND comm_payed = 0;
 

Y si, técnicamente el resultado es similar a usar GROUP BY, pero la diferencia de performance es que los registros distintos se van descartando a medida que se leen, y no al final, como en el GROUP BY.
Cuestiones de algoritmos de consulta.
En cuanto a tutoriales... más que en tutoriales, son temas que se tratan en los manuales. Te conviene ir al manual de referencia y leer los subtemas de cada uso, porque allí está la explicación de cómo MySQL optimiza cada cosa.