Re: Newbie Join Question

You forgot to 'translate' the "AND (Horas.Id = Salidas.Id)" portion of the join from Access SQL to Oracle/ANSI SQL, thus a cartesian product. Try this:

SELECT
  CARGOS.NOMBRE
, SUM(HORAS.HORAS)
, SUM(SALIDAS.IMPORTE)
FROM
  CARGOS
, HORAS
, SALIDAS
WHERE HORAS.CARGO = CARGOS.ID
AND SALIDAS.CARGO = CARGOS.ID
AND HORAS.ID = SALIDAS.ID
GROUP BY
  CARGOS.NOMBRE. De nada,
Michael J. Ort

In article <8csrig$37g$1_at_nnrp1.deja.com>,   Domingo Garcia <dgsalas_at_my-deja.com> wrote:
> Hi,
>
> I think this is a newbie question, but I am unable to find an answer.
>
> We have three tables
>
> CARGOS HORAS SALIDAS
>
> ID IDHORA IDSalida
> Nombre Cargo Cargo
> Horas Importe
>
> with the follwing data:
>
> CARGOS HORAS SALIDAS
> (1,UNO) (1,1,1) (1,1,1000)
> (2,DOS) (2,1,1) (2,1,1000)
> (3,TRES) (3,1,1) (3,1,1000)
> (4,1,1) (4,1,1000)
> (5,2,1) (5,1,1000)
> (6,2,1) (6,1,1000)
>
> In Access 97, the following query:
>
> SELECT Cargos.Nombre,
> Sum([Horas]![Horas]) AS TotalHoras,
> Sum([Salidas]![Cantidad]) AS TotalSalidas
> FROM
> (Cargos INNER JOIN Horas ON Cargos.Id = Horas.Cargo)
> INNER JOIN Salidas ON
> (Cargos.Id = Salidas.Cargo) AND (Horas.Id = Salidas.Id)
> GROUP BY Cargos.Nombre;
>
> Returns:
>
> (DOS,2,2000)
> (UNO,4,4000)
>
> which is correct.
>
> We convert it into Oracle syntax this way:
>
> SELECT NOMBRE, SUM(HORAS), SUM(IMPORTE) FROM
> CARGOS, HORAS, SALIDAS WHERE
> HORAS.CARGO = CARGOS.ID AND
> SALIDAS.CARGO = CARGOS.ID
> GROUP BY NOMBRE.
>
> Then we get:
>
> NOMBRE SUM(HORA) SUM(IMPORT
> ------- ----------- -----------
> DOS 2 2000
> UNO 16 16000
>
> The value of "UNO" is not correct.
>
> I know there is some error in the SQL syntax.
>
> Can anybody point how to make it work?
>
> Thanks in advance
>
> Domingo García
> Eurolink Consultores.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Apr 10 2000 - 00:00:00 CDT