Re: Querying multiple tables

Michael Glaesemann wrote:
>
> On Aug 1, 2007, at 11:36 , Paul Malherbe wrote:
>
>> I tried your suggestion and ended up with the same problem I was
>> experiencing i.e. because there are multiple owner and tenant
>> transaction records the sum of the values are multiplying by the
>> number of transactions i.e.
>
>> Help!
>
> Well, please help us help you! Could you please provide the table
> definitions and the query?
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
Hi

Sorry about that, here is an example of what I am doing and what I would
like to achieve.

Create table rcaowm (rom_owner varchar(7), rom_name varchar(30));
Create unique index rcaowm_key1 on rcaowm (rom_owner);
Insert into rcaowm values ('OOOOOOO', 'Owners Name');

Create table rcaowt (rot_owner varchar(7), rot_date int4, rot_tramt
decimal);
Create index rcaowt_key1 on rcaowt (rot_owner);
Insert into rcaowt values ('OOOOOOO', 20070801, -3500.0);
Insert into rcaowt values ('OOOOOOO', 20070801, -500.0);
Insert into rcaowt values ('OOOOOOO', 20070801, 399.0);

Create table rcatnm (rtm_owner varchar(7), rtm_tenant varchar(7),
rtm_name varchar(30));
Create unique index rcatnm_key1 on rcatnm (rtm_owner, rtm_tenant);
Insert into rcatnm values ('OOOOOOO', 'TTTTTTT', 'Tenants Name');

Create table rcatnt (rtt_owner varchar(7), rtt_tenant varchar(7),
rtt_date int4, rtt_tramt decimal);
Create index rcatnt_key1 on rcatnt (rtt_owner, rtt_tenant);
Insert into rcatnt values ('OOOOOOO', 'TTTTTTT', 20070801, 3500.0);
Insert into rcatnt values ('OOOOOOO', 'TTTTTTT', 20070801, 500.0);

Select rom_name as "Owner_Name", rtm_name as "Tenant_Name",
sum(rot_tramt) as "Sum_Owner_Value" , sum(rtt_tramt) as
"Sum_Tenant_Value" from rcaowm join rcaowt on (rom_owner = rot_owner)
join rcatnt on (rom_owner = rtt_owner) join (select rtm_tenant, rtm_name
from rcatnm) as tenants on (rtm_tenant = rtt_tenant) group by rom_name,
rtm_name;

This query returns:

Owner_Name | Tenant_Name | Sum_Owner_Value | Sum_Tenant_Value
-------------+--------------+-----------------+------------------
Owners Name | Tenants Name | -7202.0 | 12000.0

I would like to return:

Owner_Name | Tenant_Name | Sum_Owner_Value | Sum_Tenant_Value
-------------+--------------+-----------------+------------------
Owners Name | Tenants Name | -3601.0 | 4000.0

Regards, Paul