Cita:
Empezado por poliburro
No, no es problema de ADO.
La consulta que te he posteado es la que debes utilizar, pues evita precisamente que se te dupliquen las filas. Que motor utilizas?
|
LLevas razón, no es problema de ADO, si no del SQL.
Según explican
aquí,
un "Left Join" NO PERMITE anidar un "Inner Join" en su interior, así que con tu consulta y las explicaciones de la página anterior
lo he resuelto 
asi (para los que tengan el mismo problema):
-
1º Seleccionamos los TOTALES de ingresos de cada recibo (deben salir todos, estén o no a cero o nulo)
Código SQL
[-]
SELECT R1.ID_CLAVE AS ID_RECIBO, R1.MONTH AS MONTH
SUM(ingresos.CANTIDAD) AS ING_CANTIDAD,
SUM(ingresos.IVA) AS ING_IVA,
SUM(ingresos.RETENCION) AS ING_RETENCION,
SUM(ingresos.TOTAL) AS ING_TOTAL
FROM recibos AS R1 LEFT JOIN ingresos ON R1.ID_CLAVE = ingresos.ID_RECIBO
WHERE R1.ID_INQUILI = "0000000001" AND R1.ID_PROPIED = "100000000"
GROUP BY R1.ID_CLAVE, R1.ID_MONTH
-
2º Seleccionamos los TOTALES de gastos de cada recibo (igual que en el caso anterior)
Código SQL
[-]
SELECT R2.ID_CLAVE AS ID_RECIBO, R2.MONTH AS MONTH,
SUM(gastos.CANTIDAD) AS GAST_CANTIDAD,
SUM(gastos.IVA) AS GAST_IVA,
SUM(gastos.RETENCION) AS GAST_RETENCION,
SUM(gastos.TOTAL) AS GAST_TOTAL
FROM recibos AS R2 LEFT JOIN gastos ON R2.ID_CLAVE = gastos.ID_RECIBO
WHERE R2.ID_INQUILI = "0000000001" AND R2.ID_PROPIED = "100000000"
GROUP BY R2.ID_CLAVE, R2.MONTH AS MONTH,
-
3º Como tenemos dos resultados con el mismo número de filas (los recibos), ahora podemos unirlas
Código SQL
[-]
SELECT DatIngresos.ID_RECIBO, DatIngresos.MONTH,
ING_CANTIDAD, ING_IVA, ING_RETENCION, ING_TOTAL,
GAST_CANTIDAD, GAST_IVA, GAST_RETENCION, GAST_TOTAL
FROM (
SELECT R1.ID_CLAVE AS ID_RECIBO, R1.MONTH AS MONTH
SUM(ingresos.CANTIDAD) AS ING_CANTIDAD,
SUM(ingresos.IVA) AS ING_IVA,
SUM(ingresos.RETENCION) AS ING_RETENCION,
SUM(ingresos.TOTAL) AS ING_TOTAL
FROM recibos AS R1 LEFT JOIN ingresos ON R1.ID_CLAVE = ingresos.ID_RECIBO
WHERE R1.ID_INQUILI = "0000000001" AND R1.ID_PROPIED = "100000000"
GROUP BY R1.ID_CLAVE, R1.ID_MONTH
) AS DatIngresos
INNER JOIN
(
SELECT R2.ID_CLAVE AS ID_RECIBO, R2.MONTH AS MONTH,
SUM(gastos.CANTIDAD) AS GAST_CANTIDAD,
SUM(gastos.IVA) AS GAST_IVA,
SUM(gastos.RETENCION) AS GAST_RETENCION,
SUM(gastos.TOTAL) AS GAST_TOTAL
FROM recibos AS R2 LEFT JOIN gastos ON R2.ID_CLAVE = gastos.ID_RECIBO
WHERE R2.ID_INQUILI = "0000000001" AND R2.ID_PROPIED = "100000000"
GROUP BY R2.ID_CLAVE, R2.MONTH AS MONTH,
) AS DatGastos
ON (DatIngresos.ID_RECIBO = DatGastos.ID_RECIBO)
Caso cerrado
Gracias a tod@s
Salvica