I have three tables:

TABLE_1
client_ID
Client_Name

TABLE2
Client_ID
Value1

TABLE3
client_ID
Value2

I am trying to get a query to show Client_Name from table_1,
the sum of value1 from TABLE2 for each client_ID in TABLE1, and the sum
of value2 from TABLE3 for each client_ID TABLE1.  
How can i do it ?

Thanks,
Davis

Davis <davis@davis.com> wrote on Wed, 24 May 2006 08:29:19 -0400

>I have three tables:
>
>TABLE_1
>client_ID
>Client_Name
>
>TABLE2
>Client_ID
>Value1
>
>TABLE3
>client_ID
>Value2
>
>I am trying to get a query to show Client_Name from table_1,
>the sum of value1 from TABLE2 for each client_ID in TABLE1, and the sum
>of value2 from TABLE3 for each client_ID TABLE1.
>How can i do it ?
>
>Thanks,
>Davis
>
>
Davis


SELECT T1.Client_ID,T1.Client_Name, SUM(T2.Value1), SUM(T3.Value2)
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2 ON (T2.Client_ID = T1.Client_ID)
LEFT OUTER JOIN TABLE3 T3 ON (T3.Client_ID = T1.Client_ID)
GROUP BY T1.Client_ID, T1.Client_Name

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Thanks, but i get a wrong result.



Davis.


Jeff Cook <jeffc@aspect.co.nz> wrote:

Davis <davis@davis.com> wrote on Wed, 24 May 2006 08:29:19 -0400

>I have three tables:
>
>TABLE_1
>client_ID
>Client_Name
>
>TABLE2
>Client_ID
>Value1
>
>TABLE3
>client_ID
>Value2
>
>I am trying to get a query to show Client_Name from table_1,
>the sum of value1 from TABLE2 for each client_ID in TABLE1, and the sum
>of value2 from TABLE3 for each client_ID TABLE1.
>How can i do it ?
>
>Thanks,
>Davis
>
>
Davis


SELECT T1.Client_ID,T1.Client_Name, SUM(T2.Value1), SUM(T3.Value2)
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2 ON (T2.Client_ID = T1.Client_ID)
LEFT OUTER JOIN TABLE3 T3 ON (T3.Client_ID = T1.Client_ID)
GROUP BY T1.Client_ID, T1.Client_Name

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Davis <davis@davis.com> wrote on Wed, 24 May 2006 22:35:05 -0400

>Thanks, but i get a wrong result.
>
>
>
>Davis.
>
Davis


Wrong as in "error message" or wrong as in ... what?

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Jeff Cook <jeffc@aspect.co.nz> wrote:

Davis <davis@davis.com> wrote on Wed, 24 May 2006 22:35:05 -0400

>Thanks, but i get a wrong result.
>
>
>
>Davis.
>
Davis


Wrong as in "error message" or wrong as in ... what?

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz



The result of sql is not correct. The sum of table 3 ( t3.value2) show me a incorrect sum.

See:

T1
ID-------NAME  
1          Jonh
2          Robert


t2
ID------Value
1          10.00
1          10.00
2          20.00
2          20.00


t3
ID-------Value
1           10.00
1           10.00
2           10.00
2           10.00


Expected Result
ID----------------Name-----------------Value T2----------------Value T3
1                      Jonh                        20.00                        20.00
2                      Robert                     40.00                        20.00


Wrong Result
ID----------------Name-----------------Value T2----------------Value T3
1                      Jonh                        40.00                        40.00
2                      Robert                     80.00                        40.00

Davis

> I am trying to get a query to show Client_Name from table_1,
> the sum of value1 from TABLE2 for each client_ID in TABLE1, and the sum
> of value2 from TABLE3 for each client_ID TABLE1.
> How can i do it ?

You can do this in DBISAM with a script like

SELECT Client_Id, SUM(Value1) AS Value1 INTO Memory\Temp1 FROM Table_2
GROUP BY Client_Id;
SELECT Client_Id, SUM(Value1) AS Value2 INTO Memory\Temp2 FROM Table_3;
GROUP BY Client_Id;
SELECT Client_Id,Client_Name,Value1,Value2 FROM Table_1
LEFT OUTER JOIN Memory\Temp1 T1 ON T1.Client_Id=Table_1.Client_Id
LEFT OUTER JOIN Memory\Temp2 T2 ON T2.Client_Id=Table_1.Client_Id

Remember to drop the memory tables when you are done.

John