Newbie Join Question

From: Domingo Garcia <dgsalas_at_my-deja.com>
Date: 2000/04/10
Message-ID: <8csrig$37g$1@nnrp1.deja.com>#1/1

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. Received on Mon Apr 10 2000 - 00:00:00 CDT