Consulta rebelde

[salvica]

Hola

Tengo tres tablas
- recibos: con los datos del inquilino (es la tabla base, y tiene un registro por inquilino y mes)
- ingresos: con los pagos realizados cada mes (puede no haberlos, o haber varios pagos por distintos conceptos)
- gastos: con los gastos de cada mes (igual que ingresos)

ingresos y gastos contienen el identificador del recibo al que corresponden.

Necesito hacer una consulta que devuelva los datos de cada recibo con los totales de ingresos y gastos, pero no me sale ya que me duplica datos de la tabla de gastos

SELECT recibos.ID_CLAVE, recibos.ID_INQUILI, recibos.ID_PROPIED,
recibos.YEAR, recibos.MONTH, recibos.PAGADO,
recibos.FULL_NAME, recibos.FULL_DIR, recibos.LOCALIDAD, recibos.PROVINCIA,
SUM(ingresos.CANTIDAD) AS ING_CANTIDAD,
SUM(ingresos.IVA) AS ING_IVA,
SUM(ingresos.RETENCION) AS ING_RETENCION,
SUM(ingresos.TOTAL) AS ING_TOTAL,
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 LEFT JOIN ingresos ON recibos.ID_CLAVE=ingresos.ID_RECIBO) LEFT JOIN gastos ON recibos.ID_CLAVE=gastos.ID_RECIBO
WHERE recibos.ID_INQUILI = "0000001"
AND recibos.ID_PROPIED = "1000000"
GROUP BY recibos.ID_CLAVE, recibos.ID_INQUILI, recibos.ID_PROPIED,
recibos.YEAR, recibos.MONTH, recibos.PAGADO,
recibos.FULL_NAME, recibos.FULL_DIR, recibos.LOCALIDAD, recibos.PROVINCIA

¿donde tengo el problema?

Delphi-7, Zeos v6 y conexión mediante ADO
Gracias

salvica

[poliburro]

SELECT recibos.ID_CLAVE, recibos.ID_INQUILI, recibos.ID_PROPIED,
recibos.YEAR, recibos.MONTH, recibos.PAGADO,
recibos.FULL_NAME, recibos.FULL_DIR, recibos.LOCALIDAD, recibos.PROVINCIA,
GAST_CANTIDAD,GAST_IVA, GAST_RETENCION, GAST_TOTAL, ING_CANTIDAD,
ING_IVA, ING_RETENCION, ING_TOTAL
FROM recibos
LEFT JOIN (
Select ingresos.ID_RECIBO,
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 Ingresos As Ingresos
Inner Join recibos As Recibos
On recibos.ID_INQUILI = "0000001" AND
recibos.ID_PROPIED = "1000000" And
recibos.ID_CLAVE=ingresos.ID_RECIBO
Group By ingresos.ID_RECIBO
) As DatIngresos
On recibos.ID_CLAVE=DatIngresos.ID_RECIBO
LEFT JOIN (
Select gastos.ID_RECIBO,
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 gastos As gastos
Inner Join recibos As Recibos
On recibos.ID_INQUILI = "0000001" AND
recibos.ID_PROPIED = "1000000" And
recibos.ID_CLAVE = gastos.ID_RECIBO
Group By gastos.ID_RECIBO
) As Datgastos
On recibos.ID_CLAVE = Datgastos.ID_RECIBO
Where recibos.ID_INQUILI = "0000001" AND
recibos.ID_PROPIED = "1000000"

[salvica]

Hola, poliburro, gracias por responder

Tu consulta no he podido ejecutarla, me dice que la expresión de combinación no esta admitida, serán cosas del ADO :mad::mad:

Realmente, el problema es el siguiente, si de la clausula FROM

FROM (recibos LEFT JOIN ingresos ON recibos.ID_CLAVE = ingresos.ID_RECIBO) LEFT JOIN gastos ON recibos.ID_CLAVE = gastos.ID_RECIBO

elimino una de las relaciones enlazadas con LEFT JOIN, entonces suma bien, solo presenta el problema cuando se utilizan todas las tablas.

Salvica

[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?

[salvica]

Las tablas están en formato DBF (es una aplicación vieja y necesitan sacar algunos datos), para conectarme utilizo Microsoft.Jet.OLEDB.4.0 con Zeos (ZConnection y ZQuery)

[salvica]

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í (http://www.aulaclic.es/sql/t_3_5.htm), 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 :D:D:D 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)

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)

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

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