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

Código Delphi [-]
       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]]

Código SQL [-]
 
       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

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

Código Delphi [-]
   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]

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