Re: Newbie Join Question

Thanks, Michael. It works now!

Un saludo,

Domingo García
Eurolink Consultores.

In article <8ct84j$hoe$1_at_nnrp1.deja.com>,   Michael J. Ort <michael_ort_at_my-deja.com> wrote:
> 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.
>

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